一 SqlConnection
Connection类型的对象用来连接数据源。在不同的数据提供者的内部,Connection对象的名称是不同的,在SQL Server Data Provider里叫SqlConnection,而在OLE DB Data Provider里叫OleDbConnection。
SqlConnection:表示到SqlServer数据库打开连接。此类不能被继承。
(一) SqlConnection 创建方式:
1.连接字符串
(1)windows身份验证:
string connectionString_windows = “Integrated Security=SSPI;sever=.;database=Test;connect Timeout=20”;
各参数解释
- 其中Integrated Security=SSPI中的SSPI 是 Security Support Provide Interface,表示是windows验证
- sever,表示连接的服务器,"."表示localhost
- database,需要访问服务器的具体数据库。
(2) SQL Server身份验证:
string connectionString_Sql = “server=.;uid=sa;pwd=123123;database = Test;connect Timeout=20”;
各参数解释:与上面相同
(3) 另外一种参数表示的连接字符串
另外一种参数写法
server可以写为:data source;
database 可以写为: initial catalog;
uid 可以写为 user id;
pwd可以写为:password;
connectionString = “user id=sa;password=123321;initial catalog=Test;datasource=.;connect Timeout=20”;
(4)在配置文件中配置连接字符串
a.写在connectionStrings节点下面
< connectionStrings>
< add name="==connString ==" connectionString=“server=.;uid=sa;pwd=123123;database = Test;connect Timeout=20” providerName=“System.Data.SqlClient”/>
< /connectionStrings>
b.在程序中获取连接字符串配置:
需要using System.Configuration;
string connStr = ConfigurationManager.ConnectionStrings["== connString=="];//这里的名字就是 节点< add name中的名字>
2. 创建 SqlConnection
(1)SqlConnection sqlConnection = new SqlConnection(connectionString_windows );
//或者
(2)SqlConnection = new SqlConnection();
sqlConnection.ConnectionString = connectionString_windows;
3.打开数据库
SqlConnection.Open();//连接字符串不正确,或者数据库不存在是不能Open() 的
二.SqlCommand
(一)创建SqlCommand
1.创建方式1:实例化SqlCommand 对象,传入CommandText ,必要时设置CommandType
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn1;
//CommandText中写Sql语句,可以是普通的Sql语句,也可以是存储过程名 ==
== 如果CommandText 是普通的Sql语句或者是不带参数的存储过程名,则CommandType 可以不设置,不设置的时候,默认为CommandType.Text
cmd.CommandText = “select * from T_User”;
== 如果CommandText 是一个带参数的存储过程名,则CommandType 必须设置为 CommandType.StoredProcedure==
cmd.CommandText = “AddUser”;
cmd.CommandType = CommandType.StoredProcedure;
2.创建方式2:实例化SqlCommand 时,使用有参数的构造函数,将Sql语句直接传入
string sql = “select * from T_User”;
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn1;
3.创建方式3:
SqlCommand cmd = new SqlCommand(sql,conn1);
4.创建方式4:从已实例化的SqlConnection创建而来
SqlCommand cmd = conn1.CreateCommand();
== SqlConnection 必须是Open状态==
5.创建方式5:传入事务的SqlCommand
SqlTransaction trans = conn1.BeginTransaction();
SqlCommand cmd3 = new SqlCommand(sql,conn1,trans);
(二)SqlCommand执行Sql语句的三个函数
1.ExcuteNonQuery():
执行对数据表产生影响的Sql(增、删、改),它返回执行后影响的行数
2.ExcuteScalar():
执行查询语句返回结果集的第一行第一列的值,忽略其他行或者列,返回类型为Object
3.ExcuteReader():
将CommandText发送到Connection,并生成一个SqlDataReader数据读取器,
SqlDataReader提供一个只读,单向的数据快速传递。
*所谓单向:就是只能一条一条的向下读,不可返回去读,也不可能跳读。
*所谓只读:就是SqlDataReader数据读取器中的数据是不能修改的,在SqlDataReader读取的时候,conn必须保持连接状态,
*SqlDataReader适用于获取一个数据或多条。它读取数据高效快速。
使用SqlDataReader时,实例化时最好使用如下构造函数,以保证SqlDataReader读取完关闭之后,conn也被关闭
SqlDataReader reader = cmd.ExcuteReader(CommandBehavior.CloseConnection);
使用SqlDataReader,代码如下:
string sql = "select Id,UserName,Age from T_User";
using(SqlConnection conn = new SqlConnection(sql))
{
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
SqlDataReader reader = cmd.ExcuteReader(CommandBehavior.CloseConnection);
//整个加载到DataTable中
DataTable dt = new DataTable();
dt.Load(reader)
reader.Close();//SqlDataReader在读取完后要关闭,因为构造时设置了connection也同时关闭,此时conn也关闭了
//也可以一条一条的读取。
List<User> listUser = new List<User>();
while(read.Read())//一条一条读出
{
User user = new User();
user.Id = reader.GetInt32(0);//获取第0列(查询语句中的第一个),并转换为Int类型,类似的方法还有GetString()等等
//或者,采用key的方式取出。
user.Id = (int)reader["Id"];
}
reader.Close();
}
三 SqlParameter:
SqlParameter:用来解决带参数的Sql语句或者存储过程。
如果Sql是字符串拼接,则会有Sql注入的问题:
例:如果是拼接的Sql :Select * from T_User where Id = 1,如果Sql注入的时候,在后面加一个 Or 1=1,
则整个Sql变成了 Select from T_User Where Id= 1 Or 1=1 ,则会把所有数据都检索出来
为了防止Sql注入,需要参数化Sql;参数化还可以简化string出现单引号进行拼接的问题。
1.实例化SqlParameter
(1) 方式1,使用无参构造函数
SqlParameter para1 = new SqlParameter();
para1.ParameterName = “@Name”;
para1.Value = “xjz”;
(2)方式2,使用传参构造函数,将参数名和参数值直接传入
SqlParameter para1 = new SqlParameter("@Name",“xjz”);
== 这种方式有漏洞,当参数类型为int,而传入0的时候,会不能传入,需要用以下两种方法解决这个漏洞==
1.int id = 0;
SqlParameter para2 = new SqlParameter("@Id",id);
2.SqlParameter para3 = new SqlParameter("@Id",Convert.ToInt32(0));
(3)方式3,使用传参构造函数,传入参数名和参数类型
SqlParameter para4 = new SqlParameter("@Name",SqlDbType.VarChar);
para4.Value = “xjz”;
(4)方式4,传入参数名,参数类型和参数长度
SqlParameter para4 = new SqlParameter("@Name",SqlDbType.VarChar,50);
(5)方式5,传入参数名,参数类型,参数长度,源列的名称(DataTable中的列名相对应)
SqlParameter para5 = new SqlParameter("@Name",SqlDbType.VarChar,50,“UserName”);
标记参数为输入参数或者输出参数
para5.Direction = ParameterDirection.Output;//输出参数
para5.Direction = ParameterDirection.Input;//输入参数
2.SqlParameter的添加
(1)调用需要传入输入参数的存储过程
a.先在数据库里定义一个存储过程
Create proc GetUser
@Id int
as
Begin
select Id,UserName,Age from T_User
where Id>@id
End
b. 在程序中调用上面这个带参数的存储过程
SqlConnection conn = Command.CreateConn();
//实例化SqlCommand,调用 GetUsers 存储过程。
SqlCommand cmd = new SqlCommand(“GetUsers”,conn);
*调用带参数存储过程,Command必须设置 CommandType 属性为 CommandType.StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;
== 传入参数前,先将参数清空一下==
cmd.Parameters.Clear();
== 传入输入参数,设置参数名和参数值,注意 参数名的大小写要跟存储过程中的参数名的大小写一致==
添加一个参数
cmd.Parameters.AddWithValue("@Id",10);
添加多个参数
先实例化一个参数数组:
SqlParameter[] paras = {
new SqlParameter("@Id",10),
new SqlParameter("@Name",“xjz”)
};
cmd.Parameters.AddRange(paras);
c.执行存储过程
conn.Open();
cmd的三种执行方法:
执行增删改的方法,返回受影响的行数
cmd.ExcuteNonQuery();
返回一个SqlReader对象
cmd.ExcuteReader();
返回一个值,当Select一个值得时候用这个方法,它只返回查询的第一行第一列的值,忽略其他
cmd.ExcuteScalar();
(2)调用需要传出参数的存储过程
a.在数据库中编写存储过程
create proc GetUserName
@userName nvarchar(10) output,
@id int
as
begin
select @userName = UserName from T_User where Id = @id
if(@userName<>’’’’)
return1
else
return 0
end
上面这个存储过程需要传入一个参数@id,需要一个传出参数@userName
b.在程序中调用存储过程
private static void OutputPara()
{
SqlConnection conn = Commond.CreateConn();
SqlCommand cmd = new SqlCommand("GetUserName",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Paramters.Clear();
SqlParameter paraName = cmd.Parameters.Add(new SqlParameter("@userName",SqlDbType.NVarchar,50));
cmd.Parameters.AddWithValue("@id",110);
//设置@userName为输出参数
paraName.Direction = ParameterDirection.Output;
SqlParameter paraReturn = cmd.Parameters.Add(new SqlParameter("@return",SqlDbType.Int));
//设置paraReturn获取返回值。
paraReturn.Direction = ParameterDirection.ReturnValue;
conn.Open();
cmd.ExcuteNonQuery();
conn.Close();
string userName = paraName.Value.ToString();
string returnValue = paraReturn.Value.ToString();
}
四.SqlDataAdapter
SqlDataAdapter 是DataSet和SqlServer直接按的桥接器,用于检索和保存数据。将数据库的表检索到的数据保存到内存表中。
SqlDataAdapter有一个Fill方法,通过Fill方法填充到DataSet中,并将DataSet/DataTable中的数据更新到数据库里。
SqlDataAdapter 是由很多个Command组成包括 DeleteCommand,InsertCommand,UpdateCommand,SelectCommand等等
这些Command我们可以手动自己配置,但是系统也可以自动配置Command,系统提供了一个自动配置工具CommandBuilder
SqlDataAdapter 中的Sql可以执行多个Select查询,在填充DataSet的时候,会有多个DataTable存在
(一) DataAdapter的实例化:
1.创建方式1:
会自动创建SqlConnection,并Open,在使用结束后,会Close掉。
SqlDataAdapter dal = new SqlDataAdapter(sql,ConnString);//第一个参数是sql语句,第二个参数是连接字符串。
DataSet ds = new DataSet();
//将SqlDataAdapter中的数据填充到 DataSet中
dal.Fill(ds);
这种方式每次进行一个sql查询,系统都会自动创建一个Connection对象
2.创建方式2:
构造函数传入sql语句和SqlConnection;
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlDataAdapter da2 = new SqlDataAdapter(sql,conn);
这种方式可以多次查询使用同一个SqlConnection对象,而不需要重复创建
以上两种方式:都会自动打开SqlConnection,填充数据后,会自动关闭
3.创建方式3:
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
SqlDataAdapter da3 = new SqlDataAdapter(cmd);
conn.Close();
这种方式 SqlDataAdapter可以调用传参数的存储过程,SqlCommand可以设置为传参方式
(二)DataAdapter的TableMapping属性
上面说到 SqlDataAdapter 中的Sql可以执行多个Select查询,在填充DataSet的时候,会有多个DataTable存在,而TableMapping可以给DataSet绑定的多个表指定相应的表名映射。
例如:
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand(sql,conn);
SqlDataAdapter da = new SqlDataAdapter (cmd);
DataSet ds = new DataSet();
== 不进行TableMapping绑定的时候,在DataSet中两个表的名字分别是“Table”和"Table1"==
da.TableMapping.Add(“Table”,“User”);== 将原来名字为Table的表映射为User==
da.TableMapping.Add(“Table1“,“Department”);
da.Fill(ds);
TableMapping不但可以映射表名还可以映射每个表里的列名
例如:
DataTableMapping dpUser = da.TableMapping.Add(“Table”,“User”);生成一个DataTableMapping 的映射对象
dpUser.ColumnMappings.Add(“Age”,“UseAge”);将User表中的Age列改名为UseAge