(三)ADO.NET用控制台程序写增删查改
前面一节介绍了ADO.NET用窗体应用程序连接数据库方式,这节介绍如何使用控制台应用程序编写“增”、“删”、“查”、“改”操作。在编写程序前需要使用两个命名空间:using System.Data.SqlClient;
using System.Data;
一、编写控制类(AdoNet)
定义一个用来操作增删查改操作的控制类,用来存储操作方法:
public class AdoNet
{
//声明连接字符串
public string Sqlstr = "data source={0};database={1};User id={2};pwd={3}";
//public SqlConnection Conn = new SqlConnection("server=.;database=MyDatabaseOne;user=sa;pwd=35683568");
//声明ADO类
public SqlCommand Command;
public SqlConnection Conn;
//声明构造函数,传入连接字符串进行拼接
public AdoNet(string datasource,string database,string userid,string pwd)
{
var cStr = string.Format(Sqlstr, datasource, database, userid, pwd);
Conn = new SqlConnection(cStr);
}
//------------增加
public void AdoNetAdd(string cmd)
{
//打开数据库
Conn.Open();
//使用using在命令执行完后会释放资源
using (SqlCommand slqcmd=new SqlCommand(cmd,Conn))
{
//获取要执行的sql语句
slqcmd.CommandText = cmd;
if(slqcmd.ExecuteNonQuery()>0)
{
Console.WriteLine("增加成功");
}
else
{
Console.WriteLine("增加失败");
}
}
}
//------------删除
public void AdoDelete(string cmd)
{
Conn.Open();
using (SqlCommand sqlcmd = new SqlCommand(cmd, Conn))
{
sqlcmd.CommandText = cmd;
if(sqlcmd.ExecuteNonQuery()>0)
{
Console.WriteLine("删除成功");
}
else
{
Console.WriteLine("删除失败");
}
}
}
//------------查询
public void AdoNetSate(string cmd)
{
Conn.Open();
Command = new SqlCommand(cmd, Conn);
//批量查询
/*
SqlDataAdapter ad = new SqlDataAdapter(Command);
DataTable dt = new DataTable();
ad.Fill(dt);
var s1 = dt.Select();
var arrlist1 = s1[0].ItemArray[0];
var arrlist2 = s1[0].ItemArray[1];
var arrlist3 = s1[0].ItemArray[0];
var arrlist4 = s1[0].ItemArray[1];
Console.WriteLine(arrlist1);
Console.WriteLine(arrlist2);
Console.WriteLine(arrlist3);
Console.WriteLine(arrlist4);
*/
//单条查询
SqlDataReader dataAdapter = Command.ExecuteReader();
while (dataAdapter.Read())
{
Console.WriteLine("编号:{0},\t姓名:{1},\t密码:{2}", dataAdapter["code"], dataAdapter["name"], dataAdapter["pwd"]);
}
Console.ReadKey();
}
//------------修改
public void AdoEdit(string cmd)
{
Conn.Open();
using (SqlCommand sqlcmd = new SqlCommand(cmd, Conn))
{
sqlcmd.CommandText = cmd;
if(sqlcmd.ExecuteNonQuery()>0)
{
Console.WriteLine("修改成功");
}
else
{
Console.WriteLine("修改失败");
}
}
}
}
二、编写Main方法主函数程序
static void Main(string[] args)
{
//sql连接字符串
var adoNet = new AdoNet(".", "MyDatabaseOne", "sa", "35683568");
//调用查询方法
var sqlstr = "select * from users";
adoNet.AdoNetSate(sqlstr);
//增加
/*
string strsql = "insert into users(code,name,pwd) values(4,'黄记','016676')";
adoNet.AdoNetAdd(strsql);
*/
//删除
/*
string strsql = "delete from users where code=4";
adoNet.AdoDelete(strsql);
*/
//修改
/*
string strsql = "Update users set name='黄6' where code=3";
adoNet.AdoEdit(strsql);
*/
Console.ReadKey();
}
三、查询示例: