1.Command对象
作用是向数据库发送sql语句,可进行增删改查的方法
属性包括
CommandText:是需要封装的SQL语句
Connection:是数据库连接对象
方法包括
ExecuteNonQuery() 增删改
ExecutrScalar() 返回单一结果的查询
ExecuteReader() 返回只读数据列表的查询
2.例子-查询,这里注意语句拼接部分,以后SQL语句均可按此拼接
using System;
using System.Data.SqlClient;
namespace ADONETDemo
{
internal class Program
{
private static void Main(string[] args)
{
#region 连接数据库
server 数据库 账号 密码
string connString = "Server=222.195.148.181;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
string connString = "Server=.;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//SqlConnection sqlConnection = new SqlConnection(connString);
//sqlConnection.Open();
ConnectionState可用来查看连接情况
//if (ConnectionState.Open == sqlConnection.State)
//{
// Console.WriteLine("连接成功");
//}
//sqlConnection.Close();
//if (ConnectionState.Closed == sqlConnection.State)
//{
// Console.WriteLine("关闭成功");
//}
//Console.ReadLine();
#endregion
#region 添加语句
//连接语句
string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//进行连接
SqlConnection sqlConnection = new SqlConnection(connString);
//打开连接
sqlConnection.Open();
//添加语句
string insertStr = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
//添加语句拼接,值的占位符
insertStr += "values('{0}','{1}','{2}','{3}')";
//添加语句拼接,值
insertStr = string.Format(insertStr, "王五", "男", "18348886666", "1223");
//执行对象的连接属性与执行语句属性
SqlCommand sqlCom = new SqlCommand(insertStr, sqlConnection);
//执行添加
int result = sqlCom.ExecuteNonQuery();
//判断是否添加成功
if (result > 0)
{
Console.WriteLine("插入成功");
}
else
{
Console.WriteLine("插入失败");
}
//关闭连接
sqlConnection.Close();
Console.ReadLine();
#endregion
}
}
}
3.多条添加语句一起执行
重点代码:
//多条语句拼接
string insertStr = insertStr1 + ";" + insertStr2 + ";" + insertStr3;
全部代码
using System;
using System.Data.SqlClient;
namespace ADONETDemo
{
internal class Program
{
private static void Main(string[] args)
{
#region 连接数据库
server 数据库 账号 密码
string connString = "Server=222.195.148.181;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
string connString = "Server=.;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//SqlConnection sqlConnection = new SqlConnection(connString);
//sqlConnection.Open();
ConnectionState可用来查看连接情况
//if (ConnectionState.Open == sqlConnection.State)
//{
// Console.WriteLine("连接成功");
//}
//sqlConnection.Close();
//if (ConnectionState.Closed == sqlConnection.State)
//{
// Console.WriteLine("关闭成功");
//}
//Console.ReadLine();
#endregion
#region 添加语句
连接语句
//string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
进行连接
//SqlConnection sqlConnection = new SqlConnection(connString);
打开连接
//sqlConnection.Open();
添加语句
//string insertStr = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
添加语句拼接,值的占位符
//insertStr += "values('{0}','{1}','{2}','{3}')";
添加语句拼接,值
//insertStr = string.Format(insertStr, "王五", "男", "18348886666", "1223");
执行对象的连接属性与执行语句属性
//SqlCommand sqlCom = new SqlCommand(insertStr, sqlConnection);
执行添加
//int result = sqlCom.ExecuteNonQuery();
判断是否添加成功
//if (result > 0)
//{
// Console.WriteLine("插入成功");
//}
//else
//{
// Console.WriteLine("插入失败");
//}
关闭连接
//sqlConnection.Close();
//Console.ReadLine();
#endregion
#region 多条添加语句
//连接语句
string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//进行连接
SqlConnection sqlConnection = new SqlConnection(connString);
//打开连接
sqlConnection.Open();
//添加语句
string insertStr1 = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
//添加语句拼接,值的占位符
insertStr1 += "values('{0}','{1}','{2}','{3}')";
//添加语句拼接,值
insertStr1 = string.Format(insertStr1, "王六", "男", "18348886666", "1223");
//添加语句2
string insertStr2 = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
//添加语句拼接,值的占位符
insertStr2 += "values('{0}','{1}','{2}','{3}')";
//添加语句拼接,值
insertStr2 = string.Format(insertStr2, "王七", "男", "18348886666", "1223");
//添加语句3
string insertStr3 = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
//添加语句拼接,值的占位符
insertStr3 += "values('{0}','{1}','{2}','{3}')";
//添加语句拼接,值
insertStr3 = string.Format(insertStr3, "王八", "男", "18348886666", "1223");
//多条语句拼接
string insertStr = insertStr1 + ";" + insertStr2 + ";" + insertStr3;
//执行对象的连接属性与执行语句属性
SqlCommand sqlCom = new SqlCommand(insertStr, sqlConnection);
//执行添加
int result = sqlCom.ExecuteNonQuery();
//判断是否添加成功
if (result > 0)
{
Console.WriteLine("插入成功");
}
else
{
Console.WriteLine("插入失败");
}
//关闭连接
sqlConnection.Close();
Console.ReadLine();
#endregion
}
}
}
4.插入时获取标识列值
重点代码:
//添加语句
string insertStr = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
//添加语句拼接,值的占位符
insertStr += "values('{0}','{1}','{2}','{3}');select @@IDENTITY";
//添加语句拼接,值
insertStr = string.Format(insertStr, "王九", "男", "18348886666", "1223");
所有代码:
using System;
using System.Data.SqlClient;
namespace ADONETDemo
{
internal class Program
{
private static void Main(string[] args)
{
#region 连接数据库
server 数据库 账号 密码
string connString = "Server=222.195.148.181;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
string connString = "Server=.;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//SqlConnection sqlConnection = new SqlConnection(connString);
//sqlConnection.Open();
ConnectionState可用来查看连接情况
//if (ConnectionState.Open == sqlConnection.State)
//{
// Console.WriteLine("连接成功");
//}
//sqlConnection.Close();
//if (ConnectionState.Closed == sqlConnection.State)
//{
// Console.WriteLine("关闭成功");
//}
//Console.ReadLine();
#endregion
#region 添加语句
连接语句
//string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
进行连接
//SqlConnection sqlConnection = new SqlConnection(connString);
打开连接
//sqlConnection.Open();
添加语句
//string insertStr = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
添加语句拼接,值的占位符
//insertStr += "values('{0}','{1}','{2}','{3}')";
添加语句拼接,值
//insertStr = string.Format(insertStr, "王五", "男", "18348886666", "1223");
执行对象的连接属性与执行语句属性
//SqlCommand sqlCom = new SqlCommand(insertStr, sqlConnection);
执行添加
//int result = sqlCom.ExecuteNonQuery();
判断是否添加成功
//if (result > 0)
//{
// Console.WriteLine("插入成功");
//}
//else
//{
// Console.WriteLine("插入失败");
//}
关闭连接
//sqlConnection.Close();
//Console.ReadLine();
#endregion
#region 多条添加语句
连接语句
//string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
进行连接
//SqlConnection sqlConnection = new SqlConnection(connString);
打开连接
//sqlConnection.Open();
添加语句
//string insertStr1 = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
添加语句拼接,值的占位符
//insertStr1 += "values('{0}','{1}','{2}','{3}')";
添加语句拼接,值
//insertStr1 = string.Format(insertStr1, "王六", "男", "18348886666", "1223");
添加语句2
//string insertStr2 = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
添加语句拼接,值的占位符
//insertStr2 += "values('{0}','{1}','{2}','{3}')";
添加语句拼接,值
//insertStr2 = string.Format(insertStr2, "王七", "男", "18348886666", "1223");
添加语句3
//string insertStr3 = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
添加语句拼接,值的占位符
//insertStr3 += "values('{0}','{1}','{2}','{3}')";
添加语句拼接,值
//insertStr3 = string.Format(insertStr3, "王八", "男", "18348886666", "1223");
多条语句拼接
//string insertStr = insertStr1 + ";" + insertStr2 + ";" + insertStr3;
执行对象的连接属性与执行语句属性
//SqlCommand sqlCom = new SqlCommand(insertStr, sqlConnection);
执行添加
//int result = sqlCom.ExecuteNonQuery();
判断是否添加成功
//if (result > 0)
//{
// Console.WriteLine("插入成功");
//}
//else
//{
// Console.WriteLine("插入失败");
//}
关闭连接
//sqlConnection.Close();
//Console.ReadLine();
#endregion
#region 获取标识列
//连接语句
string connString = "Server=localhost;DataBase=MyPhoneList;Uid=123;Pwd=123";//有效
//进行连接
SqlConnection sqlConnection = new SqlConnection(connString);
//打开连接
sqlConnection.Open();
//添加语句
string insertStr = "insert into PhoneList(PName,Gender,PhoneNumber,CategoryId)";
//添加语句拼接,值的占位符
insertStr += "values('{0}','{1}','{2}','{3}');select @@IDENTITY";
//添加语句拼接,值
insertStr = string.Format(insertStr, "王九", "男", "18348886666", "1223");
//执行对象的连接属性与执行语句属性
SqlCommand sqlCom = new SqlCommand(insertStr, sqlConnection);
//执行添加
int result = Convert.ToInt32(sqlCom.ExecuteScalar());
//判断是否添加成功
if (result > 0)
{
Console.WriteLine(result);
}
else
{
Console.WriteLine("插入失败");
}
//关闭连接
sqlConnection.Close();
Console.ReadLine();
#endregion
}
}
}