一,准备
创建数据库:mshDB_Debug
USE master;
GO
CREATE DATABASE mshDB_Debug
ON
(
NAME = MyDemo_data, /*指定文件的逻辑名称*/
FILENAME = 'D:\ mshDB_Debug_dat.mdf',/*物理文件名称*/
SIZE = 10,/*指定文件大小,单位MB*/
MAXSIZE = 50,/*文件最大值,单位MB*/
FILEGROWTH = 5 /*自动增量*/
)
LOG ON
(
Name = mshDB_Debug_log,
FILENAME = 'D:\ mshDB_Debug_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
创建顾客表:tb_Customer
use mshDB_Debug;
GO
CREATE TABLE tb_Customer
(
ID INT IDENTITY(1,1) PRIMARY KEY, /*ID,主键*/
Name varchar(20) NOT NULL, /*姓名*/
Sex char(1) default('0'), /*性别:男、女,默认为男*/
CustomerType char(1) default('0'), /*客户类型:普通用户、VIP用户,默认为普通*/
Phone varchar(12), /*联系电话*/
Email varchar(50), /*电子邮件*/
ContactAddress varchar(200), /*联系地址*/
Lat float, /*所在位置维度,用于在地图显示*/
Lng float, /*所在位置经度,用于在地图显示*/
Postalcode varchar(10), /*邮政编码*/
Remark varchar(50) /*备注*/
}
ExecuteNonQuery方法
对数据表的行(记录)进行增加,删除,更新操作或者处理数据定义语句(Create Table来创建表结构),数据库是不返回数据行,仅仅返回一个包含影响行数信息的整数。一般地,在执行非查询操作时,需要调用ExcuteNonQuery方法。
案例:在tb_SelCustomer表中插入一行记录
//构造连接字符串的方法
private static SqlConnectionStringBuilder getConnDbStr()
{
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();
connStr.DataSource = "192.168.1.20";
connStr.InitialCatalog = "mshDB_Debug";
connStr.UserID = "developer";
connStr.Password = "developer";
connStr.Pooling = true;
connStr.MaxPoolSize = 1000;
connStr.MinPoolSize = 1;
//connStr.AsynchronousProcessing = true; //显示说明异步操作
return connStr;
}
//拼接SQL语句的方法
private static StringBuilder getSQLStr()
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("insert into tb_Customer ");
sqlStr.Append("values(");
sqlStr.Append("'木生火','0','0','138222233**','msh@163.com','浙江省嘉兴地区',12.234556,34.222234,'314200','备注信息')");
return sqlStr;
}
//执行SQL语句
private static void ExecSqlCommandTxt()
{
//创建connection对象
SqlConnectionStringBuilder strConn = getConnDbStr();//获取连接字符串
SqlConnection conn = new SqlConnection(strConn.ConnectionString);
//创建Command对象
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = getSQLStr().ToString();
try
{
cmd.Connection.Open();
int rows = cmd.ExecuteNonQuery();//执行命令
Console.WriteLine("\nResult: {0}行受影响", rows);
}
catch(Exception e)
{
Console.WriteLine("\nError:\n{0}", e.Message);
}
finally
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
}
运行,如下:
select * from tb_Customer