1.引用命名空间：
using System.Data;
using System.Data.SqlClient;【访问SQL Server定义的类】
2.连接字符串
string connectionString
= "server=.;integrated security=true;database=mydb"
= "server=.;database=mydb;user id=sa;password=123"
= "server=.;database=mydb;uid=sa;pwd=123"
3.访问数据库
System.Data.SqlClient.SqlConnection connection
= new SqlConnection (connectionString);
a.与数据库建立连接:connection.Open();
b.断开数据库的连接:connection.Close();或者//connection.Dispose();
4.数据库操作【命令对象和事务对象】
string sql = "insert into ...";
System.Data.SqlClient.SqlCommand command = new SqlCommand(sql, connection);
command.ExecuteNonQuery(); //返回一个影响数(int)
a.以上是隐式事务下工作
b.显式事务工作
System.Data.SqlClient.SqlTransaction trans = connection.BeginTransaction();
command.Transaction = trans;
-----》
int effectCount = command.ExecuteNonQuery(); //工作内容
《-----
->trans.Commit();
->trans.Rollback();
5.访问存储过程-传入参数-传出参数
a.传入参数
string sql = "存储过程名字";
System.Data.SqlClient.SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = = CommandType.StoreProcedure;//设置存储过程
System.Data.SqlClient.SqlParameter pEnd = new SqlParameter("@end",System.Data.SqlDbType.Int);
pEnd.Value = 100; //参数对象
command.Parameters.Add(pEnd);//加入参数
-->command.ExecuteNonQuery();
b.传出参数
System.Data.SqlClient.SqlParameter pResult = new SqlParameter("@result",System.Data.SqlDbType.Int);
pResult.Direction = ParameterDirection.Output;//参数对象
command.Parameters.Add(pResult);--OK
-->int result = (int) pResult.Value;
c.Return参数
System.Data.SqlClient.SqlParameter pReturn = new SqlParameter("@returnValue",System.Data.SqlDbType.Int);
pReturn.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(pReturn);--OK
-->int returnValue = pReturn.Value as int;
6.DataReader查询
using ( SqlConnection connection = new SqlConnect(connectionString))
{
connection.Open();
string sql = "select sid,sname,salias,sage from tb_students";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader();
while(reader.Read()) //返回boolean
{
int id = reader.GetInt32(0); //支持索引器 reader[0] eader["sid"];
string name = reader.GetString(1);
string alias = null;
if(!reader.IsDBNull(2))
{
salias = reader.GetString(2);
}
int? age = null; //可空类型,用于值类型
age = reader.GetInt32(3);
if(age.HasValue)
{
age.value;
}
}
reader.Dispose();
}
7.读取单个的值-数据访问接口-数据访问对象
a.第一行，第一列：
object obj = command.ExecuteScalar();
System.Data.SqlClient.SqlConnection
System.Data.SqlClient.SqlCommand
System.Data.SqlClient.SqlTransaction
System.Data.SqlClient.SqlParameter
Command.ExecuteNonQuery
Command.ExecuteReader
Command.ExecuteScalar
8.数据访问对象的实现
如：scope_identity()
string connectionString = "server=.;database=mydb;integrated security=true;";
using(SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "insert into ...;select scope_identity();";
SqlCommand command = new SqlCommand(sql, connection);
SqlParameter pval1 = new SqlParameter("@pval1",SqlDbType.ValChar,10);
SqlParameter pval2 = new SqlParameter("@pval2",SqlDbType.ValChar,10);
pval1.value="111";
pval2.value="222";
command.Parameters.Add(pval1);
command.Parameters.Add(pval2);
connection.Open();
object obj = command.ExecuteScalor(); //numeric(38,0)
}
9.DataTable
System.Data.DataTable table = new System.Data.DataTable();
System.Data.DataColumn idColumn
= new System.Data.DataColumn("sid",System.Type.GetType("System.Int32"));
= new System.Data.DataColumn("sid",typeof(int));
System.Data.DataColumn nameColumn = new System.Data.DataColumn("sid",typeof(string));
//表结构
table.Columns.Add(idColumn);
table.Columns.Add(nameColumn);
//主键约束
table.ParmaryKey = new DataColumn[]{ idColumn };
idColumn.AllowDBNull = false;
//idColumn.Unique
...
DataRow row = table.NewRow();
row[idColum]=1;
row[1]="Jasonlny";
table.Rows.Add(row);
foreach(DataRow dr in table.Rows)
{
...dr[..]
}
==========================================
DataTable table = new DataTable();
string connectionString = "server=.;database=mydb;integrated security=true;";
using(SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "select ... ...";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
using(SqlDataReader reader = command.ExecuteReader())
｛
int columnCount = reader.FieldCount;
for(int i=0; i<columnCount; i++)
{
DataColumn column = new DataColumn(reader.GetName(i), reader.GetFieldType(i));
table.Columns.Add(column);
}
while(reader.Read())
{
DataRow dr = table.NewRow();
for(int i=0; i<columnCount; i++)
{
dr[i] = reader[i];
}
table.Rows.Add(dr);
}
｝
}
10.DataAdapter-DataSet
System.Data.DataSet dset = new System.Data.DataSet();
//dset.Tables...
string connectionString = "...";
string sql = "select ...";
System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(sql,connectionString);
adapter.Fill(dset);
System.Data.DataTable table = dset.Tables[0];
//adapter.SelectCommand...