SQL Server 的连接、查询与更新
#Region " 命名空间 "
Imports System.Data
Imports System.Data.SqlClient
#End Region
Public Class DBCommon
Implements IDisposable
#Region " 成员变量 "
Private conn As SqlConnection
Private cmd As SqlCommand
Private trans As SqlTransaction
#End Region
#Region " 构造函数 "
Public Sub New()
Connection()
End Sub
#End Region
#Region " 过程函数 "
Public Sub Dispose() Implements IDisposable.Dispose
Close()
End Sub
Public Sub Close()
If conn Is Nothing Then
Return
End If
conn.Close()
conn.Dispose()
conn = Nothing
End Sub
Public Sub ClearParameter()
cmd.Parameters.Clear()
End Sub
Public Sub AddParameter( _
ByVal ParameterName As String, _
ByVal SqlDbType As SqlDbType, _
ByVal Size As Integer, _
ByVal Value As Object)
cmd.Parameters.Add(ParameterName, SqlDbType, Size).Value = Value
End Sub
Public Sub Fill( _
ByVal dtResult As DataTable, _
ByVal strSqlBun As String, _
ByVal Parameter As SqlParameter)
Dim objAdpt As SqlDataAdapter
objAdpt = New SqlDataAdapter(strSqlBun, conn)
cmd.CommandText = strSqlBun
objAdpt.SelectCommand = cmd
objAdpt.Fill(dtResult)
End Sub
Public Sub ExecuteNonQuery(ByVal strSqlBun As String)
cmd.CommandText = strSqlBun
cmd.ExecuteNonQuery()
End Sub
Public Sub BeginTransaction()
trans = conn.BeginTransaction()
End Sub
Public Sub Commit()
trans.Commit()
End Sub
Public Sub Rollback()
trans.Rollback()
End Sub
Protected Overrides Sub Finalize()
Close()
MyBase.Finalize()
End Sub
Private Sub Connection()
Dim strConnectionString As String
strConnectionString = My.MySettings.Default.ConnectionString
conn = New SqlConnection(strConnectionS
相关文档：
普通行列转换
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(� ......
1、 用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；在查询时，不要过多地使用 ......
for ACCESS :
update a, b set a.name=b.name1 where a.id=b.id
for SQL Server:
"update a set a.name=b.name1 from a,b where a.id=b.id"
update a set a.status=b.status
from table1 a,table2 b
&nbs ......
最近发现我们公司的ASP.NET的代码有拼接SQL语句的习惯！这是非常危险的。以下我举例说明一下
例子1：
statement := "SELECT * from users WHERE name = '" + userName + "'; "
将用户名变量(即username)设置为：
a' or 't'='t，此时原始语句发生了变化：
SELECT * from users WHERE name = 'a' OR 't'='t';
如果这 ......
MySQL:
SELECT column from table
ORDER BY RAND()
LIMIT 1
PostgreSQL:
SELECT column from table
ORDER BY RANDOM()
LIMIT 1
Microsoft SQL Server:
SELECT TOP 1 column from table
ORDER BY NEWID()
IBM DB2
SELECT column, RAND() as IDX
from table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Thanks Ti ......
