目录
(4)SqlDataAdapter(其实SqlDataAdapter 与SqlCommand可同级讨论)
一、ADO.NET知识简图
二、以Sql Server为例,讲解基本语法
1、连接字符串
private readonly static string connectionStr = ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;
2、Connection连接
//创建链接对象
using (SqlConnection connection = new SqlConnection(connectionStr))
{
connection .Open();
}
//using 相当于如下代码,确保连接对象一定会关闭
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionStr);
connection.Open();
}
catch
{
connection.Colse();
}
3、Command命令
SqlCommand command = new SqlCommand();
command.Test = commendTest; //sql字符串或存储过程名称
command.CommandType = CommandType.StoreProcedure;
或
command.CommandType = CommandType.Test;
(1)ExecuteNonQuery()方法
执行命令对象的SQL语句,返回一个int 类型的变量,返回数据库操作之后影响的行数。适合用于来验证对数据库进行增、删、改的情况。并且支持存储过程、支持事务。
sqlHelper中方法:(无事务)
public static int ExecuteNonQuery(CommandType commandType, string cmdText, params SqlParameter[] para)
{
using (SqlConnection conn = new SqlConnection(connectionStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = commandType;
cmd.CommandText = cmdText;
if (para != null)
{
cmd.Parameters.AddRange(para);
}
conn.Open();
//执行命令
int result = cmd.ExecuteNonQuery();
return Convert.ToInt32(result);
}
}
sqlHelper中方法:(有事务)
1)显式调用
public static int ExecuteNonQuery(CommandType commandType, string cmdText, params SqlParameter[] para)
{
//首先打开
SqlConnection conn = new SqlConnection(connectionStr);
conn.Open();
//开始事务
SqlTransaction transaction = conn.BeginTransaction();
//执行修改操作
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
//指定此命令位于哪个事务中
cmd.CommandType = commandType;
//执行SQL语句或者存储过程
cmd.CommandText = cmdText;
if (para != null)
{
cmd.Parameters.AddRange(para);
}
//执行到此处,说明语句没问题,SQL有语法错误则进入catch块
transaction.Commit();
//执行命令
int result = cmd.ExecuteNonQuery();
return Convert.ToInt32(result);
}
catch (SqlException e)
{
transaction.Rollback();
throw e;
}
finally
{
conn.Close();
conn.Dispose();
}
}
2)隐式调用(不建议使用)
在此不详细介绍,想了解参考文章末尾的第一条”参考文章“。
注意:
事务精确控制的话,需要在DLL层或sqlHelper中特殊问题特殊处理;非精确控制可以在sqlHelper中完成。
(2)ExecuteScalar()方法
建议在Select查询数据库时使用该方法,查询结果返回结果集中的第一行第一列,而忽略其它行和列。ExecuteScalar()方法返回一个最基本的类型Object类型,可以转换为任意类型,所以使用前需强制转换。适合用于执行SQL语句查询,支持存储过程。
sqlHelper中方法:
/// <summary>
/// 查询-执行查询,并返回查询所返回的结果集中第一行的第一列
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] pars)
{
using (SqlConnection conn = new SqlConnection(connectionStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = type;
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
conn.Open();
return cmd.ExecuteScalar();
}
}
}
(3)ExecuteReader()方法
在Select查询数据库时使用该方法,该方法的目的是尽可能快的对数据库进行查询并得到结果。适合用于执行SQL语句查询,支持存储过程。
sqlHelper中方法:
/// <summary>
/// 读取器SqlDataReader
/// </summary>
/// <param name="commandType">命令类型,如果是sql语句,则为CommandType.Text,否则为CommandType.StoredProcdure</param>
/// <param name="cmdText">SQL语句或者存储过程名称</param>
/// <param name="para">SQL参数,如果没有参数,则为null</param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(CommandType commandType, string cmdText, params SqlParameter[] para)
{
SqlConnection conn = new SqlConnection(connectionStr);
SqlDataReader readr = null;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = commandType;
cmd.CommandText = cmdText;
if (para != null)
{
cmd.Parameters.AddRange(para);
}
conn.Open();
readr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//使用CommandBehavior.CloseConnection,则当关闭readr.Close()时,conn.colse()也将关闭
return readr;
}
DLL层的调用:
/// <summary>
/// 在数据库中查找用户信息,
/// </summary>
/// <param name="userName">用户名称</param>
/// <param name="userPwd">登陆密码</param>
/// <returns></returns>
public T_UserInfo GetUserInfoModel(string userName, string userPwd)
{
string sql = "select * from T_UserInfo where UserName=@UserName and UserPwd=@UserPwd";
SqlParameter[] pars = {
new SqlParameter("@UserName",SqlDbType.NVarChar,32),
new SqlParameter("@UserPwd",SqlDbType.NVarChar,32)
};
pars[0].Value = userName;
pars[1].Value = userPwd;
CommandType commandType = CommandType.Text;
SqlDataReader readr = SqlHelper.ExecuteReader(commandType,sql, pars);
T_UserInfo userInfo = null;
while (readr.Read())
{
userInfo = new T_UserInfo();
userInfo.Id = Convert.ToInt32(readr["Id"]);
userInfo.UserName = readr["UserName"].ToString();
userInfo.UserPwd = readr["UserPwd"].ToString();
userInfo.UserMail = readr["UserMail"].ToString();
userInfo.RegTime = Convert.ToDateTime(readr["RegTime"]);
}
readr.Close();//关闭executereader,呼应sqlHelper中的CommandBehavior.CloseConnection来关闭数据库连接
return userInfo;
}
注意:reader还可以直接放在DataTable 中,如下示例
DataTable dt = new DataTable();
dt.Load(readr);
(4)SqlDataAdapter(其实SqlDataAdapter 与SqlCommand可同级讨论)
sqlHelper中方法:
/// <summary>
/// 获取DataSet数据
/// </summary>
/// <param name="sql">sql查询语句</param>
/// <param name="type">存储过程</param>
/// <param name="pars">参数</param>
/// <returns></returns>
public static DataSet GetDataSet(string sql, CommandType type, params SqlParameter[] pars)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter atper = new SqlDataAdapter(sql, conn))
{
atper.SelectCommand.CommandType = type;
if (pars != null)
{
atper.SelectCommand.Parameters.AddRange(pars);
}
DataSet ds = new DataSet();
atper.Fill(ds);
return ds;
}
}
}
注意:atper还可以直接放在DataTable 中,如下示例
DataTable da = new DataTable();
atper.Fill(da);
区别:SqlDataReader与SqlDataAdapter+DataSet
一,SqlDataReader //基于连接,只读访问 适合数据量较小。(连接模式)
SqlDataAdapter //基于非连接,适于数据量较大时,可以另行修改,最后再把修改结果返回给数据库。要求资源也大一点 (断开模式)
二,SqlDataAdapter 读取数据后将数据集放入DataSet ,DataSet 的数据存在本地客服机内存。
三,SqlDataReader返回的是一个数据读写器,只能一条条的读,操作起来不灵活,一般在只读的时候才用到。
SqlDataAdapter返回的是数据集或者表,可以对其中的数据作任意操作。
四,写法上不同:
SqlDatReader执行前须先打开数据库,然后须生成一个command对象。再由command.ExeceReader()方法赋值。完成后须手动关闭联接。
SqlCommand cmd = new SqlCommand("select * from stu", conn);
conn.Open();
SqlDataReader read= cmd.ExecuteReader();
。。。。。
read.Close();
conn.close();
SqlDataAdapter 执行时,自动打数据库,且不用Command的ExecuteReader方法进行赋值,完成后自动断开联接。
SqlDataAdapter adptr = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adptr.Fill(ds, "stu");
4、拼接字符串和SQL注入安全隐患的解决办法
(1)拼接SQL语句
1)string
2)string.Fromat()
3)stringBuider sb = new stringBuider("........");
sb.Append();
4)stringBuffer sb = new stringBuffer("........");
sb.Append();
(2)使用”参数-Parameters“解决安全隐患
1)SqlCommand
string sql = "delete from T_UserInfo Where UserName= @UserName and UserPwd=@UserPwd";
SqlCommand cmd = new SqlCommand(sql, conn); //创建SqlCommand对象
方法1:
cmd.Parameters.AddWithValue("@UserName", UserName);
cmd.Parameters.AddWithValue("@UserPwd", UserPwd); //将实际参数userName传递给SqlCommand对象(cmd)的形式参数@userName
方法2:
cmd.Parameters.Add(new SqlParameter(@UserName,UserName));
cmd.Parameters.Add(new SqlParameter(@UserPwd,UserPwd));
方法3:
SqlParameter[] pars = {
new SqlParameter("@UserName",SqlDbType.NVarChar,32),
new SqlParameter("@UserPwd",SqlDbType.NVarChar,32)
};
pars[0].Value = userName;
pars[1].Value = userPwd;
cmd.Parameters.AddRange(para);
2)SqlDataAdapter
由于这里没有出现SqlCommand对象,因此无法利用SqlCommand对象的参数传递方式完成SqlDataAdapter对象的传递参数,这里有两种方法可以实现参数的传递,如下
方法1:首先创建SqlCommand对象,并在该对象中传递参数,然后再创建SqlDataAdapter对象,设置其SelectCommand(或InsertCommand, DeleteCommand, UpdateCommand)属性值即可。
string sql = "delete from T_UserInfo Where UserName= @UserName and UserPwd=@UserPwd";
//SqlDataAdapter利用SqlCommand传递参数
SqlCommand cmd = new SqlCommand(sql, conn); //创建SqlCommand对象
cmd.Parameters.AddWithValue("@UserName ", UserName );
cmd.Parameters.AddWithValue("@UserPwd", UserPwd);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
方法2:直接对SqlDataAdapter对象进行传递参数,注意与相应的命令对应(即SelectCommand,InsertCommand, UpdateCommand, DeleteCommand)。
string sql = "delete from T_UserInfo Where UserName= @UserName and UserPwd=@UserPwd";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//直接使用DataAdapter传递参数
da.SelectCommand.Parameters.AddWithValue("@UserName ", UserName );
da.SelectCommand.Parameters.AddWithValue("@UserPwd", UserPwd);
DataSet ds = new DataSet();
da.Fill(ds);
或
string sql = "delete from T_UserInfo Where UserName= @UserName and UserPwd=@UserPwd";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlParameter[] pars = {
new SqlParameter("@UserName",SqlDbType.NVarChar,32),
new SqlParameter("@UserPwd",SqlDbType.NVarChar,32)
};
pars[0].Value = userName;
pars[1].Value = userPwd;
da.SelectCommand.Parameters.AddRange(pars);
DataSet ds = new DataSet();
da.Fill(ds);
参考文章:
c# ADO.NET 一般操作:https://blog.csdn.net/dayi521/article/details/80898796
ADO.NET结构-来自MSDN:http://www.cnblogs.com/herbert/archive/2010/07/08/1773819.html
C# ADO.NET SqlDataAdapter中传递参数:https://www.cnblogs.com/wusir/p/3545867.html
ADO.NET——C#:https://blog.csdn.net/vlllllv/article/details/84348147