参考上一篇:数据库连接配置app.config
写一个通用的数据库访问类。
public class SqlHelper //数据库连接类 { //获取数据库连接字符串,其属于静态变量且只读,项目中所有文档可以直接使用,但不能修改 public static readonly string connectString = ConfigurationManager.ConnectionStrings["CoonStr"].ConnectionString; //负责查询 ,二个参数(sql查询语句,参数) public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) { DataTable dt = new DataTable(); using (SqlConnection con = new SqlConnection(connectString)) { SqlDataAdapter adapter = new SqlDataAdapter(sql, con); adapter.SelectCommand.Parameters.AddRange(parameters); adapter.Fill(dt); } return dt; }
之后调用的时候:
//查找 歌名、歌手名、路径 string sql = "select sname,sername,spath from songs,singers where songs.Serid=Singers.Serid and Singers.Serid=@id"; DataTable dt=SqlHelper .ExecuteDataTable (sql ,new SqlParameter("@id",singer.serid )); //拼连接字符串 string sql = "select * from Users where Uname=@name"; DataTable dt = SqlHelper.ExecuteDataTable(sql, new SqlParameter("@name", strName));
增删改
//负责增删改 public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection con = new SqlConnection(connectString)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { con.Open(); cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } } //拼接查询语句 string sql = "Insert into Users(Uname,Upwd,Ubrith) values (" +"'"+Uname+"','"+Upwd+"','"+Ubrith+"')" ; DAL.SqlHelper.ExecuteNonQuery(sql); MessageBox.Show("增加会员成功");