SQLServer
System.Data.SqlClient
Access
System.Data.OleClient
SQLConnection,SQLCommand,SQLDataReader
SQLCommand属性：Connection
CommandText
CommandType
函数：ExecuteReader() select语句
ExecuteNonQuery() insert,update,delete语句
ExecuteScalar()
执行返回唯一值SQL语句
eg: string sql=”select count(*) fromstudent”; //记录个数
string sql=”select max(sage) fromstudent”;
string sql=”select min(sage) fromstudent”;
string sql=”select avg(sage) fromstudent”;
string sql=”select sum(sage) fromstudent”;
stringsql = "delete fromcustomers where customerid='" + cid + "'";
stringsql = "delete from customers where customerid='1001'";
string sql = "insertinto customers values('"+cid+"','"+cname+"','"+cphone+"')";
string sql = "insertinto customers values(@cid,@cname,@cphone)";
@参数名 SQLParameter：参数名称、参数类型（数据库）、参数大小、参数值、参数方向。
eg:
public static string connstr = @"DataSource=.\zpsqlserver;Initial Catalog=mydb;User ID=sa;Password=4846";
static void addCustomer(string cid, string cname, string cphone)
{
string sql = "insert into customersvalues(@cid,@cname,@cphone)";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();//打开连接
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;//命令对象Connection属性
cmd.CommandText = sql;//命令文本SQL
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "@cid";
p1.SqlDbType = SqlDbType.Char;
p1.Size = 20;
p1.Value = cid;
cmd.Parameters.Add(p1);//向命令对象参数集合中添加参数
SqlParameter p2 = new SqlParameter();
p2.ParameterName = "@cname";
p2.SqlDbType = SqlDbType.Char;
p2.Size = 30;
p2.Value = cname;
cmd.Parameters.Add(p2);//向命令对象参数集合中添加参数
SqlParameter p3 = new SqlParameter();
p3.ParameterName = "@cphone";
p3.SqlDbType = SqlDbType.Char;
p3.Size = 30;
p3.Value = cphone;
cmd.Parameters.Add(p3);//向命令对象参数集合中添加参数
//执行insert,delete,update命令
cmd.ExecuteNonQuery();
conn.Close();
}
static void deleteCustomer(string cid)
{
SqlConnection conn = new SqlConnection(connstr);
conn.Open();//打开连接
string sql = "delete from customers wherecustomerid='" + cid + "'";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;//命令对象Connection属性
cmd.CommandText = sql;//命令文本SQL
//执行insert,delete,update命令
cmd.ExecuteNonQuery();
conn.Close();
}
static void Main(string[] args)
{
addCustomer("1006","zhou","010-1212");
Console.ReadLine();
}
使用string.Format函数
public static string connstr = @"DataSource=.\zpsqlserver;Initial Catalog=mydb;User ID=sa;Password=4846";
static void addCustomer(string cid, string cname, string cphone)
{
//string sql = "insert into customersvalues(@cid,@cname,@cphone)";
string sql = "insert into customers values('{0}','{1}','{2}')";
sql = string.Format(sql,cid, cname, cphone);
SqlConnection conn = new SqlConnection(connstr);
conn.Open();//打开连接
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;//命令对象Connection属性
cmd.CommandText = sql;//命令文本SQL
cmd.ExecuteNonQuery();
conn.Close();
}
class SQLHelper
{
private static string connstr = @"Data Source=.\zpsqlserver;InitialCatalog=mydb;User ID=sa;Password=4846";
private static SqlConnectionconn = new SqlConnection(connstr);
private static SqlCommandcmd = new SqlCommand("", conn);
/*执行查询语句，返回DataReader*/
public static SqlDataReaderExecuteQuery(string sql)
{
if(conn.State==ConnectionState.Closed)
conn.Open();
cmd.CommandText = sql;
SqlDataReaderdr=cmd.ExecuteReader();
return dr;
}
/*执行insert,update,delete语句*/
public static int ExecuteUpdate(string sql)
{
if (conn.State == ConnectionState.Closed)
conn.Open();
cmd.CommandText = sql;
int x=cmd.ExecuteNonQuery();
conn.Close();
return x;
}
/*关闭连接对象*/
public static void CloseConnection()
{
if (conn != null && conn.State!= ConnectionState.Closed)
conn.Close();
}
}
测试类：
class Ex1_1
{
static void addCustomer(string cid, string cname, string cphone)
{
string sql = "insert into customersvalues('{0}','{1}','{2}')";
sql = string.Format(sql, cid,cname, cphone);
SQLHelper.ExecuteUpdate(sql);
}
static void deleteCustomer(string cid)
{
string sql = "delete from customers wherecustomerid='"+cid+"'";
SQLHelper.ExecuteUpdate(sql);
}
static void dispCustomers()
{
string sql = "select * from customers";
SqlDataReader dr= SQLHelper.ExecuteQuery(sql);
while (dr.Read())
{
Console.WriteLine("{0},{1},{2}",dr[0],dr[1],dr[2]);
}
SQLHelper.CloseConnection();
}
static void Main(string[] args)
{
dispCustomers();
Console.ReadLine();
}
}
class Customer
{
private string cid, cname, cphone;
public Customer() { }
public Customer(string cid, string cname, string cphone)
{
this.cid = cid; this.cname = cname; this.cphone= cphone;
}
/*属性访问器*/
}
class CustomerDao
{
public void addCustomer(Customercus)
{
string sql = "insert into customers values('{0}','{1}','{2}')";
sql = string.Format(sql, cus.Cid,cus.Cname, cus.Cphone);
SQLHelper.ExecuteUpdate(sql);
}
public void deleteCustomer(string cid)
{
string sql = "delete from customers wherecustomerid='" + cid + "'";
SQLHelper.ExecuteUpdate(sql);
}
public List<Customer> queryAllCustomers()
{
List<Customer> clist = newList<Customer>();
string sql = "select * from customers";
SqlDataReader dr = SQLHelper.ExecuteQuery(sql);
while (dr.Read())
{
Customer cus = new Customer(dr[0],dr[1],dr[2]);
clist.Add(cus);
}
SQLHelper.CloseConnection();
return clist;
}
}
三层架构下的测试函数
class Ex1_1
{
static void Main(string[] args)
{
CustomerDao dao = new CustomerDao();
List<Customer> clist = dao.queryAllCustomers();
Console.WriteLine(clist.Count);
Console.ReadLine();
}
}