三、C#连接SQL Server数据库
添加引用:
using System.Data;
using System.Data.SqlClient;
**(1)创建连接:**连接数据库的类DBConn
public class SQLConn
{
/// <summary>
/// 创建连接数据库的方法
/// </summary>
/// <returns>返回连接</returns>
//创建连接数据库的方法
public SqlConnection OpenConn()
{
//创建连接
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = "127.0.0.1";
scsb.UserID = "sa";
scsb.Password = "123";
scsb.InitialCatalog = "Apexbio";
//打开连接
SqlConnection conn = new SqlConnection(scsb.ToString());//参数:连接数据库的字符串
if (conn.State == System.Data.ConnectionState.Closed)//判断连接的开启状态,如果未连接是关闭的
conn.Open();//打开连接
return conn;
}
}
(2)连接数据库
//拼接T-SQL语句
string strSQL = "SELECT * FROM userinfo WHERE user_id='"+user_id+"'AND user_pwd='"+user_pwd+"'";
//执行SQL语句
SQLConn sqlConn = new SQLConn();//实例化连接SQL数据库的类的对象
SqlConnection conn = sqlConn.OpenConn();//调用对象中打开数据库的方法
SqlCommand comm = new SqlCommand(strSQL,conn);//实例化SQLCommand对象
SqlDataReader dr = comm.ExecuteReader();//执行SQL语句,并接收返回受影响的行数
if (dr.Read())//如果行数大于0,则说明用户名和密码没问题
{
dr.Close();
return;
}
return;
注:SQL语句拼接:
增:
string strSQL = @"INSERT INTO [dbo].[userinfo]
([user_id]
,[user_name]
,[user_pwd])
VALUES
('" + user.user_id + @"'
,'" + user.user_name + @"'
,'" + user.user_pwd + @"')";
改:
string strSQL = @"UPDATE [dbo].[userinfo] SET
[user_id]='" + user.user_id + @"'
,[user_name]='" + user.user_name + @"'
,[user_pwd]='" + user.user_pwd+ @"'
WHERE [user_id]='"+user.user_id+"'";
删除:
string strSQL = @"DELETE [dbo].[userinfo]
WHERE [user_id]='" + user.user_id + "'";
删除方法:
public string DeleteStudent(User user)
{
//拼接T-SQL语句
string strSQL = @"DELETE [dbo].[userinfo]
WHERE [user_id]='" + user.user_id + "'";
//执行SQL语句
SQLConn sqlConn = new SQLConn();//实例化连接SQL数据库的类的对象
SqlConnection conn = sqlConn.OpenConn();//调用对象中打开数据库的方法
SqlCommand comm = new SqlCommand(strSQL, conn);//实例化SQLCommand对象
try
{
int row_count = comm.ExecuteNonQuery();//执行SQL语句,并接受返回的受影响行数
}
catch (Exception ex)
{
return ex.Message;
}
return "用户信息删除成功";
}
四、datagridview绑定数据库
private void StuSearch_Load(object sender, EventArgs e)
{
Class.UserCtrl ss = new Class.UserCtrl();
DataTable dt = ss.SearchUser();
dataGridView1.DataSource = dt.DefaultView;
}
UserCtrl类中的SearchUser方法:
public DataTable SearchUser()
{
//拼接T-SQL语句
string strSQL = @"SELECT [user_id],[user_name] FROM [dbo].[userinfo]";
//执行SQL语句
SQLConn sqlConn = new SQLConn();//实例化连接SQL数据库的类的对象
SqlConnection conn = sqlConn.OpenConn();//调用对象中打开数据库的方法
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);//参数1 :T-SQL脚本,参数2:
da.Fill(ds, "user");//填充数据源
dt = ds.Tables["user"];//获取数据源中的表
return dt;
}