一:连接数据库之查询学生个数
查询学生个数代码如下:
private void button1_Click(object sender, EventArgs e)
{
string s = "server=.;database=SampleDb;integrated security=true;";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = s;
SqlCommand cmd = new SqlCommand();
string sql = "select count(*) from student";
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
object o = cmd.ExecuteScalar();
int n = (int)o;
conn.Close();
MessageBox.Show("学生共有" + n + "个学生");
}
功能如下:
举个例子 :cmd.ExecuteNonQuery() 用于执行 update ,delete 等操作。可以返回更新了多少行,或者删除了多少行。 cmd.ExecuteScalar(); 可以用来返回像 select count(StudentName)from Students where Sex="男" 这样的语句。返回一个结果值(这里是男生的人数)。
二:实现一个简单的登录页面
代码如下:
private void button4_Click(object sender, EventArgs e)
{
string s = "server=.;database=SampleDb;integrated security=true;";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = s;
SqlCommand cmd = new SqlCommand();
string sql = string.Format("select count(*) from student where Id='{0}'and name='{1}'",
textBox1.Text,textBox2.Text);
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
object o = cmd.ExecuteScalar();
int n = (int)o;
conn.Close();
if(n>0)
MessageBox.Show("登陆成功");
}
运行如下:
三:用sqldatareader实现读取学生功能
代码如下:
private void button1_Click(object sender, EventArgs e)
{
string s = "server=.;database=SampleDb;integrated security=true;";
SqlConnection conn = new SqlConnection(s);
SqlCommand cmd = new SqlCommand();
string sql = "select * from student";
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
string id, name, gender, major;
int grade, cls;
object o;
while (reader.Read())
{
o = reader["id"];
id=(string) o;
name = (string)reader["name"];
gender = (string)reader["gender"];
major = (string)reader["major"];
grade = (int)reader["grade"];
cls = (int)reader["class"];
string temp=string.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\n",
id, name, gender, major, grade, cls);
textBox1.AppendText(temp);
}
reader.Close();
conn.Close();
}
运行如下:
四:实现条件查询
用性别和年级
代码如下:
private void button4_Click(object sender, EventArgs e)
{
textBox1.Text = "";
string s = "server=.;database=SampleDb;integrated security=true;";
SqlConnection conn = new SqlConnection(s);
SqlCommand cmd = new SqlCommand();
string sql = "select * from student ";
string where = " where 1=1 ";
if (textBox2.Text != "")
where = where + " and grade=" + textBox2.Text;
if (textBox3.Text != "")
where = where + " and gender='" + textBox3.Text + "'";//字符串需加引号
sql = sql + where;
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
string id, name, gender, major;
int grade, cls;
object o;
while (reader.Read())
{
o = reader["id"];
id = (string)o;
name = (string)reader["name"];
gender = (string)reader["gender"];
major = (string)reader["major"];
grade = (int)reader["grade"];
cls = (int)reader["class"];
string temp = string.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\n",
id, name, gender, major, grade, cls);
textBox1.AppendText(temp);
}
reader.Close();
conn.Close();
}
运行如下:
五:用datagirdview实现数据的查询和更删改查
代码如下;
显示的:
private void button1_Click(object sender, EventArgs e)
{
string s = "server=.;database=SampleDb;integrated security=true;";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = s;
ds = new DataSet();
adapter = new SqlDataAdapter("select * from student",conn);
adapter.Fill(ds);
grid.DataSource = ds.Tables[0];
}
运行图:
及时保存的;
private void button2_Click(object sender, EventArgs e)
{
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(ds);
MessageBox.Show("保存成功!");
}
运行图: