C#对数据库的操作(增删改查)
1、【在web.config文件中配置】
<connectionStrings> <add name="sqlConnection" connectionString="Data Source=服务器;Initial Catalog=数据库名称;Persist Security Info=True;User ID=用户名;Password=密码" providerName="System.Data.SqlClient"/> </connectionStrings>
2、【连接字符串】
private static readonly string StrCon = ConfigurationManager.ConnectionStrings["sqlConnection"].ToString();
3、【查询数据方法】
/// <summary> /// 查询数据 /// </summary> /// <param name="sqlStr">查询语句</param> /// <param name="parameter">参数</param> /// <returns></returns> public static DataTable QueryData(string sqlStr,params SqlParameter [] parameter) { try { using (SqlConnection conn = new SqlConnection()) { 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); } }
4、【更新数据方法】
/// <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) ; } }
5、【删除数据方法】
/// <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); } }
6、【添加数据方法】
/// <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); } }
7、写个方法调用【查询数据方法】,其他三个方法调用与此类似
public DataTable GetInfo(string id) { var sqlStr = "select * from Student where id=@id"; DataTable table = SqlHelper.QueryData(sqlStr, new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@id",id) }); return table; }