用WinForm,实现基本的功能操作
学了这几天的WinForm,弄了几个基本查询,插入,修改,删除···等功能,简单记录自己的学习过成,感觉在有链接数据库的时候,开始是每个按钮都用了连接数据库代码,总觉得很累赘,想了办法,定义了个全局变量,解决问题,链接数据库的代码有几种,先说查询吧
Code
SqlConnection myConn;//定义全局变量
SqlDataAdapter da;
DataSet ds;
myConn = new SqlConnection();
myConn.ConnectionString = "server=localhost;database=StuMagSys;Trusted_Connection=True"; //SQL数据库
private void btnSearch_Click(object sender, System.EventArgs e)//查询
{
string strDBName, strTable;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
if (txtStudentID.Text.Length == 0)
{
MessageBox.Show("请输入查询的学生学号!!");
return;
}
else
{
myConn = new SqlConnection();
myConn.ConnectionString = "server=localhost;database=" + strDBName + ";Trusted_Connection=True"; //SQL数据库
da = new SqlDataAdapter(strSQL, myConn);
ds = new DataSet();
da.Fill(ds, strTable);
txtTable.Text = ds.Tables[0].TableName.ToString();
txtTable.Text = ds.Tables[strTable].TableName.ToString();
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = ds;
dataGridView1.DataSource=ds.Tables[strTable].DefaultView;
myDr.Close();
myConn.Close();
}
myDr.Close();
myConn.Close();
}
很粗糙,但可以运行,其实当我们要从数据库中读取数据就得先和数据库建立联系,完了,读出操作,读出的数据放在SqlDataAdapter da 中,在把此数据加载到DataSet ds 中,最后用dataGridView 显示。做了查询,就是插入,修改,删除操作,都很简单,就一步说了吧,SqlConnection myConn;//定义全局变量
SqlDataAdapter da;
DataSet ds;
myConn = new SqlConnection();
myConn.ConnectionString = "server=localhost;database=StuMagSys;Trusted_Connection=True"; //SQL数据库
private void btnSearch_Click(object sender, System.EventArgs e)//查询
{
string strDBName, strTable;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
if (txtStudentID.Text.Length == 0)
{
MessageBox.Show("请输入查询的学生学号!!");
return;
}
else
{
myConn = new SqlConnection();
myConn.ConnectionString = "server=localhost;database=" + strDBName + ";Trusted_Connection=True"; //SQL数据库
da = new SqlDataAdapter(strSQL, myConn);
ds = new DataSet();
da.Fill(ds, strTable);
txtTable.Text = ds.Tables[0].TableName.ToString();
txtTable.Text = ds.Tables[strTable].TableName.ToString();
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = ds;
dataGridView1.DataSource=ds.Tables[strTable].DefaultView;
myDr.Close();
myConn.Close();
}
myDr.Close();
myConn.Close();
}
Code
private void btnAdd_Click(object sender, EventArgs e)
{
string strDBName, strTable;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
myConn.Open();
string strStudentID = txtStudentID.Text;
string strStudentName = txtStudentName.Text;
string strHome = txtHome.Text;
string strJob = txtJob.Text;
string strPhone = txtPhone.Text;
string strSex = txtSex.Text;
string strSpecialty = txtSpecialty.Text;
//第一种查询
//string strSQL = "INSERT INTO Student(Student_id,Student_name,Sex,Home,Job,Specialty,Phone) VALUES ('"+strStudentID+"','"+strStudentName+"','"+strSex+"','"+strHome+"','"+strJob+"','"+strSpecialty+"','"+strPhone+"')";
//SqlCommand myCmd = myConn.CreateCommand();
//myCmd.CommandText = strSQL;
//myCmd.ExecuteNonQuery();
//MessageBox.Show("成功新增加一条记录!!");
//第二种查询
DataRow myRow = ds.Tables[strTable].NewRow();
myRow["Student_id"] = strStudentID;
myRow["Student_name"] = strStudentName;
myRow["Sex"] = strSex;
myRow["Home"] = strHome;
myRow["Job"] = strJob;
myRow["Specialty"] = strSpecialty;
myRow["Phone"] = strPhone;
ds.Tables[strTable].Rows.Add(myRow);
dataGridView1.Refresh();
myConn.Close();
}
private void btnEdit_Click(object sender, EventArgs e)
{
string strDBName, strTable;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
myConn.Open();
string strStudentID = txtStudentID.Text;
string strStudentName = txtStudentName.Text;
string strHome = txtHome.Text;
string strJob = txtJob.Text;
string strPhone = txtPhone.Text;
string strSex = txtSex.Text;
string strSpecialty = txtSpecialty.Text;
//第一种查询
//string strSQL = "UPDATE Student SET Student_name='" + strStudentName + "'," + "Sex='" + strSex + "'," + "Home='" + strHome + "'," + "Job='" + strJob + "'," + "Specialty='"+strSpecialty+"',"+"Phone='"+strPhone+"'";
//SqlCommand myCmd = myConn.CreateCommand();
//myCmd.CommandText = strSQL;
//myCmd.ExecuteNonQuery();
//MessageBox.Show("成功更新了学号为"+strStudentID+"的学生信息!!");
//第二种查询
DataColumnCollection myDCC = ds.Tables[strTable].Columns;
DataColumn[] myDC = { myDCC["Student_id"] };
ds.Tables[strTable].PrimaryKey = myDC;
DataRow myRow = ds.Tables[strTable].Rows.Find(strStudentID);
if (myRow == null)
{
MessageBox.Show("找不到客户编号为" + strStudentID + "的客户");
}
myRow["Student_id"] = strStudentID;
myRow["Student_name"] = strStudentName;
myRow["Sex"] = strSex;
myRow["Home"] = strHome;
myRow["Job"] = strJob;
myRow["Specialty"] = strSpecialty;
myRow["Phone"] = strPhone;
dataGridView1.Refresh();
myConn.Close();
}
private void btnDelete_Click(object sender, EventArgs e)
{
string strDBName, strTable;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
myConn.Open();
string strStudentID = txtStudentID.Text;
//第一种查询
//string strSQL = "DELETE FROM Student WHERE Student_id=" + strStudentID;
//SqlCommand myCmd = myConn.CreateCommand();
//myCmd.CommandText = strSQL;
//int num = myCmd.ExecuteNonQuery();
//if(num==0)
// MessageBox.Show("不能删除学号为" + strStudentID + "的学生记录,或许该记录不存在!!");
//else
// MessageBox.Show("成功删除学号为"+strStudentID+"的学生记录!!");
//第二种查询
DataColumnCollection myDCC = ds.Tables[strTable].Columns;
DataColumn[] myDC = { myDCC["Student_id"] };
ds.Tables[strTable].PrimaryKey = myDC;
DataRow myRow = ds.Tables[strTable].Rows.Find(strStudentID);
if (myRow == null)
{
MessageBox.Show("找不到学号为" + strStudentID + "的学生记录");
}
ds.Tables[strTable].Rows.Remove(myRow);
dataGridView1.Refresh();
MessageBox.Show("成功删除学号为" + strStudentID + "的学生记录!!");
myConn.Close();
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
myConn.Close();
}
private void btnSQL_Click(object sender, EventArgs e)
{
myConn.Open();
string strSQL = txtSQL.Text;
if (txtSQL.Text.Length == 0)
{
MessageBox.Show("请输入SQL语句!!");
return;
}
SqlDataAdapter da = new SqlDataAdapter(strSQL,myConn); //第一张表
DataSet ds = new DataSet();
da.Fill(ds, "Student");
dataGridView1.DataSource = ds;
myConn.Close();
}
private void btnRenovate_Click_1(object sender, EventArgs e)
{
string strDBName, strTable,strST;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
strST="SELECT * FROM "+strTable+" ";
if (txtTable.Text == "")//判断是否输入数据库名称
{
MessageBox.Show("请输入要连接的数据库名称和表名!!");
}
else
{
myConn.Open();
SqlDataAdapter da = new SqlDataAdapter(strST, myConn);
DataSet ds = new DataSet();
da.Fill(ds, strTable);
txtTable.Text = ds.Tables[0].TableName.ToString();
txtTable.Text = ds.Tables[strTable].TableName.ToString();
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = ds;
dataGridView1.DataMember = strTable;
if (myConn.State == ConnectionState.Open)
{
//显示状况信息
txtConnection.Text = "数据库 " + strDBName + " 已经连接成功!!";
}
myConn.Close();
}
}
其中可以自己在Form上编写SQL语句,虽然这些都很简单,但还是很基础,作为一个新手那是一种收获。private void btnAdd_Click(object sender, EventArgs e)
{
string strDBName, strTable;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
myConn.Open();
string strStudentID = txtStudentID.Text;
string strStudentName = txtStudentName.Text;
string strHome = txtHome.Text;
string strJob = txtJob.Text;
string strPhone = txtPhone.Text;
string strSex = txtSex.Text;
string strSpecialty = txtSpecialty.Text;
//第一种查询
//string strSQL = "INSERT INTO Student(Student_id,Student_name,Sex,Home,Job,Specialty,Phone) VALUES ('"+strStudentID+"','"+strStudentName+"','"+strSex+"','"+strHome+"','"+strJob+"','"+strSpecialty+"','"+strPhone+"')";
//SqlCommand myCmd = myConn.CreateCommand();
//myCmd.CommandText = strSQL;
//myCmd.ExecuteNonQuery();
//MessageBox.Show("成功新增加一条记录!!");
//第二种查询
DataRow myRow = ds.Tables[strTable].NewRow();
myRow["Student_id"] = strStudentID;
myRow["Student_name"] = strStudentName;
myRow["Sex"] = strSex;
myRow["Home"] = strHome;
myRow["Job"] = strJob;
myRow["Specialty"] = strSpecialty;
myRow["Phone"] = strPhone;
ds.Tables[strTable].Rows.Add(myRow);
dataGridView1.Refresh();
myConn.Close();
}
private void btnEdit_Click(object sender, EventArgs e)
{
string strDBName, strTable;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
myConn.Open();
string strStudentID = txtStudentID.Text;
string strStudentName = txtStudentName.Text;
string strHome = txtHome.Text;
string strJob = txtJob.Text;
string strPhone = txtPhone.Text;
string strSex = txtSex.Text;
string strSpecialty = txtSpecialty.Text;
//第一种查询
//string strSQL = "UPDATE Student SET Student_name='" + strStudentName + "'," + "Sex='" + strSex + "'," + "Home='" + strHome + "'," + "Job='" + strJob + "'," + "Specialty='"+strSpecialty+"',"+"Phone='"+strPhone+"'";
//SqlCommand myCmd = myConn.CreateCommand();
//myCmd.CommandText = strSQL;
//myCmd.ExecuteNonQuery();
//MessageBox.Show("成功更新了学号为"+strStudentID+"的学生信息!!");
//第二种查询
DataColumnCollection myDCC = ds.Tables[strTable].Columns;
DataColumn[] myDC = { myDCC["Student_id"] };
ds.Tables[strTable].PrimaryKey = myDC;
DataRow myRow = ds.Tables[strTable].Rows.Find(strStudentID);
if (myRow == null)
{
MessageBox.Show("找不到客户编号为" + strStudentID + "的客户");
}
myRow["Student_id"] = strStudentID;
myRow["Student_name"] = strStudentName;
myRow["Sex"] = strSex;
myRow["Home"] = strHome;
myRow["Job"] = strJob;
myRow["Specialty"] = strSpecialty;
myRow["Phone"] = strPhone;
dataGridView1.Refresh();
myConn.Close();
}
private void btnDelete_Click(object sender, EventArgs e)
{
string strDBName, strTable;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
myConn.Open();
string strStudentID = txtStudentID.Text;
//第一种查询
//string strSQL = "DELETE FROM Student WHERE Student_id=" + strStudentID;
//SqlCommand myCmd = myConn.CreateCommand();
//myCmd.CommandText = strSQL;
//int num = myCmd.ExecuteNonQuery();
//if(num==0)
// MessageBox.Show("不能删除学号为" + strStudentID + "的学生记录,或许该记录不存在!!");
//else
// MessageBox.Show("成功删除学号为"+strStudentID+"的学生记录!!");
//第二种查询
DataColumnCollection myDCC = ds.Tables[strTable].Columns;
DataColumn[] myDC = { myDCC["Student_id"] };
ds.Tables[strTable].PrimaryKey = myDC;
DataRow myRow = ds.Tables[strTable].Rows.Find(strStudentID);
if (myRow == null)
{
MessageBox.Show("找不到学号为" + strStudentID + "的学生记录");
}
ds.Tables[strTable].Rows.Remove(myRow);
dataGridView1.Refresh();
MessageBox.Show("成功删除学号为" + strStudentID + "的学生记录!!");
myConn.Close();
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
myConn.Close();
}
private void btnSQL_Click(object sender, EventArgs e)
{
myConn.Open();
string strSQL = txtSQL.Text;
if (txtSQL.Text.Length == 0)
{
MessageBox.Show("请输入SQL语句!!");
return;
}
SqlDataAdapter da = new SqlDataAdapter(strSQL,myConn); //第一张表
DataSet ds = new DataSet();
da.Fill(ds, "Student");
dataGridView1.DataSource = ds;
myConn.Close();
}
private void btnRenovate_Click_1(object sender, EventArgs e)
{
string strDBName, strTable,strST;
strDBName = txtDBName.Text;
strTable = txtTable.Text;
strST="SELECT * FROM "+strTable+" ";
if (txtTable.Text == "")//判断是否输入数据库名称
{
MessageBox.Show("请输入要连接的数据库名称和表名!!");
}
else
{
myConn.Open();
SqlDataAdapter da = new SqlDataAdapter(strST, myConn);
DataSet ds = new DataSet();
da.Fill(ds, strTable);
txtTable.Text = ds.Tables[0].TableName.ToString();
txtTable.Text = ds.Tables[strTable].TableName.ToString();
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = ds;
dataGridView1.DataMember = strTable;
if (myConn.State == ConnectionState.Open)
{
//显示状况信息
txtConnection.Text = "数据库 " + strDBName + " 已经连接成功!!";
}
myConn.Close();
}
}