C#笔记 ADO.NET操作SQL SERVER数据库
本文演示在C#中使用ADO.NET组件连接SQL SERVER 2005数据库文件,并对其中的数据表进行相应的操作。涉及的数据存取对像有SqlConnection、SqlCommand、SqlDataAdapter、SqlDataReader,还有临时的数据表存储对象DataSet。具体的使用方法,大家可以参考下面的代码自己体会一下。
注意:需要引用命名空间System.Data.SqlClient
控制数据流的类DataProcess:![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
class DataProcess
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
static string conn = "Data source=.;Initial Catalog=test;Integrated Security=true";
string sql = "";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
List<Student> studentList = new List<Student>();
DataSet ds = new DataSet();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlConnection sqlConn = new SqlConnection(conn);
SqlCommand command;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public void OpenConnection()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sqlConn.Open();
}
public void CloseConnection()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sqlConn.Close();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public void ShowWelcome()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine(" " + "学生成绩维护");
Console.WriteLine("====================");
Console.WriteLine(" L 显示");
Console.WriteLine(" A 增加");
Console.WriteLine(" D 删改");
Console.WriteLine(" E 修改");
Console.WriteLine(" Q 退出");
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public void StudentList(string tableName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string sql = "select * from " + tableName;
SqlDataAdapter da = new SqlDataAdapter(sql, sqlConn);
da.Fill(ds, "成绩表");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//PadRight使字符个数为定长,用来控制显示形式
//这里将int型变量前置,完全是为了显示结果的美观
Console.WriteLine("学号".PadRight(8) + "成绩".PadRight(8) + "姓名".PadRight(8));
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string stuNum = ds.Tables[0].Rows[i][0].ToString();
string name = ds.Tables[0].Rows[i][1].ToString();
string grade= ds.Tables[0].Rows[i][2].ToString();
Console.WriteLine(stuNum.PadRight(10) + grade.PadRight(10) + name.PadRight(10));
}
ds.Tables[0].Clear();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//添加学生信息
public void Add()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("请输入学生编号:");
int stuNum = int.Parse(Console.ReadLine());
Console.WriteLine("请输入学生姓名:");
string name = Console.ReadLine();
Console.WriteLine("请输入学生成绩:");
int grade = int.Parse(Console.ReadLine());
sql=string.Format("insert into 成绩表(学号,姓名,成绩) values({0},'{1}',{2})",stuNum,name,grade);
//实例化command对象,用于执行SQL非查询操作
command = new SqlCommand(sql, sqlConn);
command.ExecuteNonQuery();
Console.WriteLine("数据插入成功!");
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//删除学生信息
public void Delete()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("请输入学生编号");
string stuNum = Console.ReadLine();
sql = "select * from 成绩表 where 学号="+stuNum;
command = new SqlCommand(sql, sqlConn);
SqlDataReader sdr = command.ExecuteReader();
Console.WriteLine("学号".PadRight(8) + "成线".PadRight(8) + "姓名".PadRight(8));
while (sdr.Read())
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine(sdr[0].ToString().PadRight(10)+sdr[2].ToString().PadRight(10)+sdr[1].ToString().PadRight(10));
}
sdr.Close();
sql = "delete from 成绩表 where 学号="+stuNum;
command = new SqlCommand(sql, sqlConn);
command.ExecuteNonQuery();
Console.WriteLine("学生信息已删除!");
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public void Modify()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("请输入学生编号");
string stuNum = Console.ReadLine();
sql = "select * from 成绩表 where 学号=" + stuNum;
command = new SqlCommand(sql, sqlConn);
SqlDataReader sdr = command.ExecuteReader();
Console.WriteLine("学号".PadRight(8) + "成线".PadRight(8) + "姓名".PadRight(8));
while (sdr.Read())
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine(sdr[0].ToString().PadRight(10) + sdr[2].ToString().PadRight(10) + sdr[1].ToString().PadRight(10));
}
sdr.Close();
Console.WriteLine("请输入学生姓名:");
string name = Console.ReadLine();
Console.WriteLine("请输入学生成绩:");
string grade = Console.ReadLine();
sql = string.Format("update 成绩表 set 姓名='{0}',成绩={1} where 学号={2}",name,grade,stuNum);
command = new SqlCommand(sql, sqlConn);
command.ExecuteNonQuery();
Console.WriteLine("学生信息修改成功!");
}
}
上面的类中,List泛型创建的对象其实是没用的,上载时自己疏忽了。(-_-)!!!呵呵。
控制台的显示:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
class Program
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
static void Main(string[] args)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataProcess dp = new DataProcess();
dp.ShowWelcome();
while (true)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
ConsoleKeyInfo cki = new ConsoleKeyInfo();
cki = Console.ReadKey(true);
switch (char.ToUpper(cki.KeyChar))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
case 'L':
dp.OpenConnection();
dp.StudentList("成绩表");
dp.CloseConnection();
break;
case 'A':
dp.OpenConnection();
dp.Add();
dp.CloseConnection();
break;
case'D':
dp.OpenConnection();
dp.Delete();
dp.CloseConnection();
break;
case'E':
dp.OpenConnection();
dp.Modify();
dp.CloseConnection();
break;
default:
dp.ShowWelcome();
break;
}
}
}
}
注意:需要引用命名空间System.Data.SqlClient
控制数据流的类DataProcess:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
控制台的显示:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)