ADO.NET技术,该技术用于.net程序操作数据库
ADO的组成:
1.数据提供程序
a.Connection:连接对象,用于指定数据库服务器的相关信息(数据库服务名 数据库用户名 密码等)
b.Command:命令对象,用于对数据库执行增删改查的操作,会提供对应的操作方法
2.数据集
对数据库的操作:
1.导名称空间System.Data.SqlClient (用于连接Sql server数据库)
2.创建连接对象
//连接字符串的格式: server=数据库服务名;database=数据库;uid=用户名;pwd=密码
String conStr = "server=192.168.0.102;database=MySchool3;uid=sa;pwd=sa";
SqlConnection cn = new SqlConnection(conStr);
3.打开连接
cn.open();
4.创建命令对象
String sql = "delete student where studentId="+this.textBox1.Text;
SqlCommand cmd = new SqlCommand(sql,con);
5.执行命令对象
int count=cmd.ExecuteNonQuery();//该方法的返回值:sql语句执行后 受影响的行数
ExecuteNonQuery方法用于执行增删改
6.关闭连接对象
cn.close();
SqlHelper类：
/// <summary> /// 数据库操作通用类 /// 达到了sql防注入，并且，将对数据库的操作独立开来，可以提高内聚，降低耦合。达到了哪里使用，哪里关闭的效果，避免隐患。 /// </summary> public class SqlHelper { SqlConnection conn = null; SqlCommand com = null; SqlDataReader rd = null; string constr = System.Configuration.ConfigurationManager.ConnectionStrings["Family"].ToString(); /// <summary> /// 打开数据库连接 /// </summary> /// <returns></returns> public bool ConnectSql() { try { conn = new SqlConnection(constr); conn.Open(); return true; } catch { return false; } } /// <summary> /// 关闭数据库连接 /// </summary> public void closeConn() { try { if (conn != null) { conn.Close(); } if (rd != null) { rd.Close(); } } catch { return; } } /// <summary> /// 数据增删改 /// </summary> /// <param name="sql">sql语句</param> /// <param name="dic">数据集合对象</param> /// <returns></returns> public static bool SqlPour(string sql, Dictionary<string, string> dic) { try { ConnectSql(); com = new SqlCommand(sql, conn); if (dic != null) { foreach (var item in dic) { com.Parameters.AddWithValue(item.Key, item.Value); } } com.ExecuteNonQuery(); return true; } catch (Exception e) { Console.WriteLine(e.Message); return false; } finally { closeConn(); } } /// <summary> /// 查询数据返回ArrayList /// </summary> /// <param name="sql">sql语句</param> /// <param name="dic">数据集合对象</param> /// <returns></returns> public static ArrayList SelectInfo(string sql, Dictionary<string, string> dic) { try { ConnectSql(); com = new SqlCommand(sql, conn); ArrayList al = new ArrayList(); if (dic != null) { foreach (var item in dic) { //遍历参数并进行赋值，防止sql注入 com.Parameters.AddWithValue(item.Key, item.Value); } } rd = com.ExecuteReader(); int clumn = 0; //得到数据的列数 if (rd.Read()) { clumn = rd.FieldCount; } else { return null; } do { //读取每行每列的数据并放入Object数组中 Object[] obj = new object[clumn]; for (int i = 0; i < clumn; i++) { obj[i] = rd[i]; } al.Add(obj); //将一行数据放入数组中 } while (rd.Read()); return al; } catch { return null; } finally { closeConn(); } } }
调用：
//----------------------------公共类 sqlHelper 调用------------------------------------------ /// <summary> /// 根据条件查询 /// </summary> /// <param name="UserID">参数</param> /// <param name="UserCreationtime">参数</param> /// <returns></returns> public static bool GetInfo(string UserID, DateTime UserCreationtime) { string sql = "select * from [User] where UserID=@UserID and UserCreationtime=@UserCreationtime"; Dictionary<string, string> dic = new Dictionary<string, string>(); dic.Add("@UserID", UserID); dic.Add("@UserCreationtime", UserCreationtime.ToString()); ArrayList al = SqlHelper.SelectInfo(sql, dic); //判断是否为空，空为false if (al.Count > 0) { //得到几行数据就能产生多少个对象 foreach (Object[] obj in al) { User uset = new User(); uset.UserID = obj[0].ToString(); //参数转换，强转很容易发生异常，所以数据库的约束性要强，对象类时要认真检查数据类型，多用try...catch uset.UserCreationtime = Convert.ToDateTime(obj[1]); } } else { return false; } return true; } /// <summary> /// 增加数据 /// </summary> /// <param name="UserleverlType">参数</param> /// <returns></returns> public static bool GetAdd(string UserlevelID, string UserleverlType) { Dictionary<string, string> dic = new Dictionary<string, string>(); var strSql = "insert into [level] (UserlevelID, UserleverlType)values(@UserlevelID,@UserleverlType)"; //放置占位符 dic.Add("@UserlevelID", UserlevelID); dic.Add("@UserleverlType", UserleverlType); //将其放入字典（类似JSON，采用键值对的方式传递） if (!SqlHelper.SqlPour(strSql, dic)) { return false; } return true; } /// <summary> /// 修改数据 /// </summary> /// <param name="UserName">参数</param> /// <param name="UserSet">参数</param> /// <returns></returns> public static bool GetUpdate(string UserName, string UserSet, string UserID) { Dictionary<string, string> dic = new Dictionary<string, string>(); var strSql = "update [User] set UserName=@UserName, UserSet=@UserSet where UserID=@UserID"; dic.Add("@UserID", UserID); dic.Add("@UserName", UserName); dic.Add("@UserSet", UserSet); dic.Add("@UserCreationtime", DateTime.Now.ToString()); if (!SqlHelper.SqlPour(strSql, dic)) { return false; } return true; } /// <summary> /// 根据条件删除数据 /// </summary> /// <param name="UserID">参数</param> /// <returns></returns> public static bool GetDel(string UserID) { Dictionary<string, string> dic = new Dictionary<string, string>(); string sql = "DELETE FROM [User] WHERE UserID=@UserID"; dic.Add("UserID", UserID); if (!SqlHelper.SqlPour(sql, dic)) { return false; } return true; }