MVC-EF数据操作（一）
EF数据操作
新建一个类库，命名为DBUtility
DbHelper类
该类主要执行各种SQL语句，定义一个私有的string类型数据库连接串conStr，在构造里面给其赋值
1 /// <summary> 2 /// 数据库连接串 3 /// </summary> 4 private string conStr;// = ConfigurationManager.ConnectionStrings["DbConStr"].ConnectionString; 5 6 public DbHelper(string dbConStr) 7 { 8 conStr = dbConStr; 9 }
执行简单的SQL语句的各个方法
1 /// <summary> 2 /// 返回DataTable 3 /// </summary> 4 /// <param name="strSql"></param> 5 /// <returns></returns> 6 public DataTable GetDataTable(string strSql) 7 { 8 try 9 { 10 using (SqlConnection con = new SqlConnection(conStr)) 11 { 12 DataSet ds = new DataSet(); 13 SqlDataAdapter da = new SqlDataAdapter(strSql, con); 14 da.Fill(ds); 15 return ds.Tables[0]; 16 } 17 } 18 catch (Exception ex) 19 { 20 return null; 21 throw ex; 22 } 23 } 24 25 /// <summary> 26 /// 执行sql,返回受影响的行数 27 /// </summary> 28 /// <param name="strSql"></param> 29 /// <returns></returns> 30 public bool ExecuteSql(string strSql) 31 { 32 try 33 { 34 using (SqlConnection con = new SqlConnection(conStr)) 35 { 36 SqlCommand command = new SqlCommand(strSql, con); 37 con.Open(); 38 return command.ExecuteNonQuery() > 0; 39 } 40 } 41 catch 42 { 43 return false; 44 } 45 } 46 47 /// <summary> 48 /// 执行SQL命令 49 /// </summary> 50 /// <param name="cmd"></param> 51 /// <returns></returns> 52 public int ExecuteCommand(SqlCommand cmd) 53 { 54 try 55 { 56 using (SqlConnection con = new SqlConnection(conStr)) 57 { 58 cmd.Connection = con; 59 con.Open(); 60 return cmd.ExecuteNonQuery(); 61 } 62 } 63 catch 64 { 65 return -1; 66 } 67 } 68 69 /// <summary> 70 /// 执行sql,返回首行首列 71 /// </summary> 72 /// <param name="strSql"></param> 73 /// <returns></returns> 74 public object ExecuteScalar(string strSql) 75 { 76 try 77 { 78 using (SqlConnection con = new SqlConnection(conStr)) 79 { 80 SqlCommand command = new SqlCommand(strSql, con); 81 con.Open(); 82 return command.ExecuteScalar(); 83 } 84 } 85 catch (Exception ex) 86 { 87 throw ex; 88 } 89 } 90 91 /// <summary> 92 /// 执行事务 93 /// </summary> 94 /// <param name="sqlStrList"></param> 95 /// <returns></returns> 96 public bool ExecuteSqlTran(List<string> sqlStrList) 97 { 98 try 99 { 100 using (SqlConnection con = new SqlConnection(conStr)) 101 { 102 con.Open(); 103 using (SqlTransaction sqlTransaction = con.BeginTransaction()) 104 { 105 try 106 { 107 foreach (string sqlStr in sqlStrList) 108 { 109 SqlCommand command = new SqlCommand(sqlStr, con); 110 command.Transaction = sqlTransaction; 111 command.ExecuteNonQuery(); 112 } 113 sqlTransaction.Commit(); 114 return true; 115 } 116 catch (Exception transEx) 117 { 118 sqlTransaction.Rollback(); 119 return false; 120 throw transEx; 121 } 122 } 123 } 124 } 125 catch (Exception ex) 126 { 127 return false; 128 throw ex; 129 } 130 }
执行带参数的SQL语句的方法
1 /// <summary> 2 /// 返回DataTable 3 /// </summary> 4 /// <param name="str"></param> 5 /// <param name="values"></param> 6 /// <returns></returns> 7 public DataTable GetDataTable(string str, params SqlParameter[] values) 8 { 9 try 10 { 11 using (SqlConnection con = new SqlConnection(conStr)) 12 { 13 DataSet ds = new DataSet(); 14 SqlCommand command = new SqlCommand(str, con); 15 command.Parameters.AddRange(values); 16 SqlDataAdapter da = new SqlDataAdapter(command); 17 da.Fill(ds); 18 return ds.Tables[0]; 19 } 20 } 21 catch (Exception ex) 22 { 23 return null; 24 throw ex; 25 } 26 } 27 28 /// <summary> 29 /// 执行多条SQL语句，实现数据库事务。 30 /// </summary> 31 /// <param name="sqlDic"></param> 32 public void ExecuteSqlTran(Dictionary<string, SqlParameter[]> sqlDic) 33 { 34 using (SqlConnection conn = new SqlConnection(conStr)) 35 { 36 conn.Open(); 37 using (SqlTransaction trans = conn.BeginTransaction()) 38 { 39 SqlCommand cmd = new SqlCommand(); 40 try 41 { 42 //循环 43 foreach (var dic in sqlDic) 44 { 45 SqlCommand command = new SqlCommand(dic.Key, conn); 46 if (dic.Value != null) 47 { 48 command.Parameters.AddRange(dic.Value); 49 } 50 command.Transaction = trans; 51 command.ExecuteNonQuery(); 52 } 53 trans.Commit(); 54 } 55 catch 56 { 57 trans.Rollback(); 58 throw; 59 } 60 } 61 } 62 }
