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();
DBbase
//读取配置文件，连接数据库语句 public static string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["Family"].ConnectionString; //实例化连接对象 con SqlConnection con = new SqlConnection(strCon); //检测连接是否打开 public void Connection() { if (this.con.State == ConnectionState.Closed) { this.con.Open(); } } //根据提供的strSQL语句 访问数据库，返回List集合 public List<User> GetDataSet(string strSQL) { Connection(); try { SqlDataAdapter da = new SqlDataAdapter(strSQL, con); DataSet ds = new DataSet(); da.Fill(ds); List<User> list = new List<User>(); if (ds.Tables.Count > 0) { for (int i = 0; i < ds.Tables.Count; i++) { foreach (DataRow dr in ds.Tables[i].Rows) { User obj = new User(); if (ds.Tables[i].Columns.Contains("UserID")) obj.UserID = Convert.ToString(dr["UserID"]); if (ds.Tables[i].Columns.Contains("UserName")) obj.UserName = Convert.ToString(dr["UserName"]); if (ds.Tables[i].Columns.Contains("UserSet")) obj.UserSet = Convert.ToString(dr["UserSet"]); if (ds.Tables[i].Columns.Contains("Userphone")) obj.Userphone = Convert.ToString(dr["Userphone"]); if (ds.Tables[i].Columns.Contains("UserworkType")) obj.UserworkType = Convert.ToString(dr["UserworkType"]); if (ds.Tables[i].Columns.Contains("WorkDetailsSituation")) obj.WorkDetailsSituation = Convert.ToString(dr["WorkDetailsSituation"]); if (ds.Tables[i].Columns.Contains("UserleverlType")) obj.UserleverlType = Convert.ToString(dr["UserleverlType"]); if (ds.Tables[i].Columns.Contains("UserType")) obj.UserType = Convert.ToString(dr["UserType"]); if (ds.Tables[i].Columns.Contains("UserCreationtime")) obj.UserCreationtime = Convert.ToDateTime(dr["UserCreationtime"]); if (ds.Tables[i].Columns.Contains("Userhobby")) obj.Userhobby = Convert.ToString(dr["Userhobby"]); list.Add(obj); } } } return list; } catch (Exception) { throw; } }
Helper
public class Helper { //通用的查询方法,返回查询的结果集 public static DataSet getDataSet(string sql) { //创建连接对象 string constr = System.Configuration.ConfigurationManager.ConnectionStrings["Family"].ToString(); //创建命令对象 SqlConnection cn = new SqlConnection(constr); cn.Open(); //适配器 SqlDataAdapter da = new SqlDataAdapter(sql, cn); DataSet ds = new DataSet();//没有数据 //调用da.fill方法对ds进行数据填充 da.Fill(ds); //关闭连接 cn.Close(); return ds; } //通用的增删改方法 public static int executeQuery(string sql) { //创建连接对象 string constr = System.Configuration.ConfigurationManager.ConnectionStrings["Family"].ToString(); SqlConnection cn = new SqlConnection(constr); cn.Open(); //创建命令对象 SqlCommand cmd = new SqlCommand(sql, cn); //执行命令 int count = cmd.ExecuteNonQuery(); cn.Close(); return count; } }
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(); } } }
SqlDBbase
public class SqlDBbase { /// <summary> /// 数据连接方法 /// </summary> /// <returns></returns> public static SqlConnection Connection() { //读取配置文件，连接数据库语句 string connStr = ConfigurationManager.ConnectionStrings["Family"].ConnectionString; //实例化连接对象 con SqlConnection conn = new SqlConnection(connStr); //返回对象 return conn; } /// <summary> /// 根据sql赋值到Dictionary集合 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public static Dictionary<string, object> select(string sql) { SqlConnection conn = Connection(); SqlDataAdapter da = new SqlDataAdapter(sql, conn); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count == 0) { return null; } DataRow row = dt.Rows[0]; Dictionary<string, object> dic = new Dictionary<string, object>(); foreach (DataColumn item in dt.Columns) { dic.Add(item.ColumnName, row[item.ColumnName]); } return dic; } /// <summary> /// 查询多条数据 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public static DataTable Selects(string sql) { try { SqlConnection conn = Connection(); SqlCommand com = new SqlCommand(); com.Connection = conn; com.CommandText = sql; SqlDataAdapter da = new SqlDataAdapter(com); DataSet ds = new DataSet(); da.Fill(ds); return ds.Tables[0]; } catch (Exception ex) { throw ex; } } /// <summary> /// 执行一条SQL语句，增删改都可以用 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public static int ExecutionSQl(string sql) { SqlConnection conn = Connection(); try { SqlCommand com = new SqlCommand(sql, conn); return com.ExecuteNonQuery(); } catch (Exception ex) { Console.Write(ex.Message); return -1; } finally { conn.Close(); } } /// <summary> /// 保存修改带参数可用此方法 /// </summary> /// <param name="sql">sql语句</param> /// <param name="sqlparameter">参数</param> /// <returns></returns> public static int ExecutionPar(string sql, SqlParameter[] sqlparameter) { SqlConnection conn = Connection(); SqlCommand com = null; try { com = new SqlCommand(); com.Connection = conn; com.CommandText = sql; return com.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); return -1; } finally{ com.Parameters.Clear(); conn.Close(); } } }
DBHelper
public class DBHelper { //读取配置文件，连接数据库语句 public static string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["Family"].ConnectionString; /// <summary> /// 查询数据 /// </summary> /// <param name="sqlStr">查询语句</param> /// <param name="parameter">参数</param> /// <returns></returns> public static DataTable QueryData(string sqlStr, params SqlParameter[] parameter) { try { //实例化连接对象 conn using (SqlConnection conn = new SqlConnection(strCon)) { conn.Open(); SqlCommand cmd = new SqlCommand(sqlStr, conn); DataSet dt = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(); cmd.Parameters.AddRange(parameter); adapter.SelectCommand = cmd; adapter.Fill(dt); conn.Close(); return dt.Tables[0]; } } catch (Exception ex) { throw new ApplicationException("查询数据异常" + ex.Message); } } /// <summary> /// 添加数据 /// </summary> /// <param name="sqlStr">添加语句</param> /// <param name="parameter">参数</param> /// <returns></returns> public static bool AddData(string sqlStr, params SqlParameter[] parameter) { try { using (SqlConnection conn = new SqlConnection()) { conn.Open(); SqlCommand cmd = new SqlCommand(sqlStr, conn); cmd.Parameters.AddRange(parameter); var row = cmd.ExecuteNonQuery(); conn.Close(); if (row > 0) { return true; } return false; } } catch (Exception ex) { throw new ApplicationException("添加数据异常" + ex.Message); } } /// <summary> /// 更新数据 /// </summary> /// <param name="sqlStr">更新语句</param> /// <param name="parameter">参数</param> /// <returns></returns> public static bool UpdateData(string sqlStr, params SqlParameter[] parameter) { try { using (SqlConnection conn = new SqlConnection()) { conn.Open(); SqlCommand cmd = new SqlCommand(sqlStr, conn); cmd.Parameters.AddRange(parameter); var row = cmd.ExecuteNonQuery(); conn.Close(); if (row > 0) { return true; } return false; } } catch (Exception ex) { throw new ApplicationException("更新数据异常" + ex.Message); } } /// <summary> /// 删除数据 /// </summary> /// <param name="sqlStr">删除语句</param> /// <param name="parameter">参数</param> /// <returns></returns> public static bool DeleteData(string sqlStr, params SqlParameter[] parameter) { try { using (SqlConnection conn = new SqlConnection()) { conn.Open(); SqlCommand cmd = new SqlCommand(sqlStr, conn); cmd.Parameters.AddRange(parameter); var row = cmd.ExecuteNonQuery(); conn.Close(); if (row > 0) { return true; } return false; } } catch (Exception ex) { throw new ApplicationException("删除数据异常" + ex.Message); } } }
DataTools
public class DataTools { //读取配置文件，连接数据库语句 public static string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["Family"].ConnectionString; private static OleDbConnection oleConn = new OleDbConnection(strCon);//access database /// <summary> /// 根据命令增加 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int Insert(string sql) { int result = 0; try { oleConn.Open(); OleDbCommand oleCommand = new OleDbCommand(sql, oleConn); result = oleCommand.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { oleConn.Close(); } return result; } /// <summary> /// 根据命令查询表 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public static DataTable Select(string sql) { DataTable tb = new DataTable(); try { oleConn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(sql, oleConn); adapter.Fill(tb); } catch (Exception exception) { string message = exception.Message; } finally { oleConn.Close(); } return tb; } /// <summary> /// 根据命令删除 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public static int Delete(string sql) { int ifExecute = 0; try { oleConn.Open(); OleDbCommand comm = new OleDbCommand(sql); ifExecute = comm.ExecuteNonQuery(); } finally { oleConn.Close(); } return ifExecute; } /// <summary> /// 更新数据 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public static int Update(string sql) { int ifExecute = 0; try { oleConn.Open(); OleDbCommand comm = new OleDbCommand(sql, oleConn); ifExecute = comm.ExecuteNonQuery(); } finally { oleConn.Close(); } return ifExecute; } /// <summary> /// 执行sql返回对象 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public static object ExecuteSingle(string sql) { object obj = null; try { oleConn.Open(); OleDbCommand comm = new OleDbCommand(sql, oleConn); obj = comm.ExecuteScalar(); } finally { oleConn.Close(); } return obj; } }
DAL
public class Family { //----------------------------------------DBbase类调用--------------------------------- //实例化一个DBbase对象 static DBbase db = new DBbase(); //查询用户数据 public static List<User> User() { //通过实体中的属性访问 拼接一个你需要的SQL语句 StringBuilder strSQL = new StringBuilder(); strSQL.Append("Select Theserialnumber, UserID, UserName, UserSet, Userphone, work.UserworkType,Details.WorkDetailsSituation,[level].UserleverlType,[type].UserType, UserCreationtime, hobby.Userhobby from [User] "); strSQL.Append("inner join Work on Work.UserworkID=[User].UserworkID "); strSQL.Append("inner join [level] on [level].UserlevelID=[user].UserlevelID "); strSQL.Append("inner join UserType as [type] on [type].UserTypeID=[USER].UserTypeID "); strSQL.Append("inner join WorkDetails as Details on Details.WorkDetailsID=Work.WorkDetailsID "); strSQL.Append("inner join Userhobby as hobby on hobby.UserhobbyID=[user].UserhobbyID"); return db.GetDataSet(strSQL.ToString()); } //-------------DBHelperl类调用----------------------------------------------------------- /// <summary> /// 根据条件查询 /// </summary> /// <param name="UserID">参数</param> /// <returns></returns> public static DataTable GetInfo(string UserID) { var sqlStr = "select * from [User] where UserID=@UserID"; //数据表对象获取方法数据 System.Data.SqlClient.SqlParameter参数获取 DataTable table = DBHelper.QueryData(sqlStr, new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@UserID", UserID) }); return table; } /// <summary> /// 增加数据 /// </summary> /// <param name="UserlevelID">参数</param> /// <param name="UserleverlType">参数</param> /// <returns></returns> public static bool AddData(string UserlevelID, string UserleverlType) { var sqlStr = "insert into [level] (UserlevelID, UserleverlType)values(@UserlevelID,@UserleverlType)"; bool bl = DBHelper.AddData(sqlStr, new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@UserlevelID", UserlevelID), new System.Data.SqlClient.SqlParameter("@UserleverlType", UserleverlType) }); return bl; } /// <summary> /// 修改数据 /// </summary> /// <param name="UserName">参数</param> /// <param name="UserSet">参数</param> /// <param name="UserID">参数</param> /// <returns></returns> public static bool UpdateData(string UserName, string UserSet, string UserID) { var sqlStr = "update [User] set UserName=@UserName, UserSet=@UserSet where UserID=@UserID"; bool bl = DBHelper.UpdateData(sqlStr, new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@UserID", UserID), new System.Data.SqlClient.SqlParameter("@UserName", UserName), new System.Data.SqlClient.SqlParameter("@UserSet", UserSet)}); return bl; } /// <summary> /// 删除数据 /// </summary> /// <param name="UserID">参数</param> /// <returns></returns> public static bool DeleteData(string UserID) { string sqlStr = "Delete From [User] Where UserID=@UserID"; bool bl = DBHelper.DeleteData(sqlStr, new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@UserID", UserID) }); return bl; } //----------------------------公共类 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; } }