C#连接数据库

C#连接数据库固定代码模板

删除数据库内容

​ //1.指定连接字符串
​ string connectionString = @“Data Source=LAPTOP-0BUQLA1E; Initial Catalog=test; Integrated Security=true”;
​ //2新建连接
​ SqlConnection conn = new SqlConnection(connectionString);
​ //3.打开连接
​ conn.Open();
​ //4.创建SqlCommand对象
​ SqlCommand cmd = new SqlCommand();
​ cmd.Connection = conn;
​ cmd.CommandType = CommandType.Text;
​ string sql= “delete from newstudent where sno=” + textSno.Text;
​ cmd.CommandText = sql;
​ //5.执行sql语句
​ int res=cmd.ExecuteNonQuery();
​ if (res > 0)
​ {
​ label1.Text = “删除成功”;
​ }
​ else
​ {
​ label1.Text = “删除失败”;
​ }
​ //6.关闭连接
​ conn.Close();

修改数据库内容

string connstring = @“Data Source=LAPTOP-0BUQLA1E; Initial Catalog=test; Integrated Security=true”;
SqlConnection conn = new SqlConnection(connstring);
//string sql = string.Format(“update newstudent set sname=’{0}’,gender=’{1}’,classid={2},age={3} where sno={4}”,textSname.Text,radioButton1.Checked?“男”:“女”,cmbclassid.SelectedIndex0?1:2,textAge.Text,textSno.Text);
string sql = “update newstudent set sname=@sname,gender=@gender,classid=@classid,age=@age where sno=@sno”;
SqlParameter[] pls =
{
new SqlParameter("@sno",textSno.Text),
new SqlParameter("@sname", textSname.Text),
new SqlParameter("@gender",radioButton1.Checked ? “男” : “女”),
new SqlParameter("@classid", cmbclassid.Text"一班"? 1 : 2),
new SqlParameter("@age",textAge.Text)
};
SqlCommand cmd = new SqlCommand(sql,conn);
cmd.Parameters.AddRange(pls);
conn.Open();
int res = cmd.ExecuteNonQuery();
if (res > 0)
{
//修改成功
//MessageBox.Show(“修改成功”);
this.DialogResult = DialogResult.OK;

}
else
{
//修改失败
MessageBox.Show(“修改失败”);
}
conn.Close();

查询并显示数据库数据

//1.指定连接字符串
string connectionString = @“Data Source=LAPTOP-0BUQLA1E; Initial Catalog=test; Integrated Security=true”;
//2新建连接
SqlConnection conn = new SqlConnection(connectionString);
try
{
//3.打开连接
conn.Open();
//4.执行Sql语句
string sql = “select * from newstudent”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sdr=cmd.ExecuteReader();
bindingSource1.DataSource = sdr;
dataGridView1.DataSource =bindingSource1;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//6.关闭连接
conn.Close();
}

添加数据库数据

private void btnadd_Click(object sender, EventArgs e)
{
string connstring = @“Data Source=LAPTOP-0BUQLA1E; Initial Catalog=test; Integrated Security=true”;
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string sql = string.Format(“insert into newstudent values({0},’{1}’,’{2}’,{3},{4})”, textSno.Text, textSname.Text, radioButton1.Checked ? “男” : “女”, cmbclassid.Text==“一班”? 1 : 2,textAge.Text);
SqlCommand cmd = new SqlCommand(sql, conn);
int res = cmd.ExecuteNonQuery();
if (res > 0)
{
//修改成功
//MessageBox.Show(“添加成功”);
this.DialogResult = DialogResult.OK;
}
else
{
//修改失败
MessageBox.Show(“添加失败”);
}
conn.Close();
}