C#数据库操作小结
程序员文章站
2023-12-03 15:36:40
1、常用的t-sql语句 查询:select * from tb_test where id='1' and n...
1、常用的t-sql语句
查询:select * from tb_test where id='1' and name='xia'
select * from tb_test
插入:insert into tb_test values('xia','123')
insert into tb_test(name) values('xia')
更新:update tb_test set password='234' where id='1'
删除:delete from tb_test where id='1'
delete tb_test where id='1'
2、在vs2010中获取数据库连接字符串
string connectionstring = properties.settings.default.databasetestconnectionstring;
3、sqlcommand类型
查询:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqlcommand command = new sqlcommand(selectstr, connection);
command.connection.open();
sqldatareader reader = command.executereader();
while (reader.read())
label1.text = "name:" + reader["name"].tostring(); //数据读取
command.connection.close();
}
catch (sqlexception ex)
{
throw ex;
}
}
插入、修改、删除:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqlcommand command = new sqlcommand(cmdstr, connection);
command.connection.open();
command.executenonquery();
command.connection.close();
}
catch (sqlexception ex)
{
throw ex;
}
}
4、datatable类型,查询、添加、修改、删除
datatable使用查询、添加、删除、修改时,需要用到sqldataadapter类
string selectstr = "select * from tb_test2";
查询:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqldataadapter adapter = new sqldataadapter(selectstr, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
//数据读取
label1.text = datatable.rows[0][0].tostring();
}
catch (sqlexception ex)
{
throw ex;
}
}
添加:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqldataadapter adapter = new sqldataadapter(selectstr, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
//添加数据
datarow newrow = datatable.newrow();
newrow["id"] = "tesr";
newrow["name"] = "111";
datatable.rows.add(newrow);
sqlcommandbuilder builder = new sqlcommandbuilder(adapter);
adapter.update(datatable); //更新到数据库
}
catch (sqlexception ex)
{
throw ex;
}
}
修改:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqldataadapter adapter = new sqldataadapter(selectstr, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
//修改数据
datarow updaterow = datatable.rows[0];
updaterow["id"] = "update";
updaterow["name"] = "222";
sqlcommandbuilder builder = new sqlcommandbuilder(adapter);
adapter.update(datatable); //更新到数据库
}
catch (sqlexception ex)
{
throw ex;
}
}
删除:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqldataadapter adapter = new sqldataadapter(selectstr, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
datatable.rows[0].delete(); //删除记录
sqlcommandbuilder builder = new sqlcommandbuilder(adapter);
adapter.update(datatable); //更新到数据库
}
catch (sqlexception ex)
{
throw ex;
}
}
5、dataset类型
dataset操作跟datatabel操作基本是一样的,只是dataset可以储存有多个表格,所以就多做介绍了
6、个人总结
个人感觉,用 sqlcommand比较灵活,而dataset是实现ado.net断开式连接的核心,比较安全
查询:select * from tb_test where id='1' and name='xia'
select * from tb_test
插入:insert into tb_test values('xia','123')
insert into tb_test(name) values('xia')
更新:update tb_test set password='234' where id='1'
删除:delete from tb_test where id='1'
delete tb_test where id='1'
2、在vs2010中获取数据库连接字符串
string connectionstring = properties.settings.default.databasetestconnectionstring;
3、sqlcommand类型
查询:
复制代码 代码如下:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqlcommand command = new sqlcommand(selectstr, connection);
command.connection.open();
sqldatareader reader = command.executereader();
while (reader.read())
label1.text = "name:" + reader["name"].tostring(); //数据读取
command.connection.close();
}
catch (sqlexception ex)
{
throw ex;
}
}
插入、修改、删除:
复制代码 代码如下:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqlcommand command = new sqlcommand(cmdstr, connection);
command.connection.open();
command.executenonquery();
command.connection.close();
}
catch (sqlexception ex)
{
throw ex;
}
}
4、datatable类型,查询、添加、修改、删除
datatable使用查询、添加、删除、修改时,需要用到sqldataadapter类
string selectstr = "select * from tb_test2";
查询:
复制代码 代码如下:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqldataadapter adapter = new sqldataadapter(selectstr, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
//数据读取
label1.text = datatable.rows[0][0].tostring();
}
catch (sqlexception ex)
{
throw ex;
}
}
添加:
复制代码 代码如下:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqldataadapter adapter = new sqldataadapter(selectstr, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
//添加数据
datarow newrow = datatable.newrow();
newrow["id"] = "tesr";
newrow["name"] = "111";
datatable.rows.add(newrow);
sqlcommandbuilder builder = new sqlcommandbuilder(adapter);
adapter.update(datatable); //更新到数据库
}
catch (sqlexception ex)
{
throw ex;
}
}
修改:
复制代码 代码如下:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqldataadapter adapter = new sqldataadapter(selectstr, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
//修改数据
datarow updaterow = datatable.rows[0];
updaterow["id"] = "update";
updaterow["name"] = "222";
sqlcommandbuilder builder = new sqlcommandbuilder(adapter);
adapter.update(datatable); //更新到数据库
}
catch (sqlexception ex)
{
throw ex;
}
}
删除:
复制代码 代码如下:
using (sqlconnection connection = new sqlconnection(connectionstring))
{
try
{
sqldataadapter adapter = new sqldataadapter(selectstr, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
datatable.rows[0].delete(); //删除记录
sqlcommandbuilder builder = new sqlcommandbuilder(adapter);
adapter.update(datatable); //更新到数据库
}
catch (sqlexception ex)
{
throw ex;
}
}
5、dataset类型
dataset操作跟datatabel操作基本是一样的,只是dataset可以储存有多个表格,所以就多做介绍了
6、个人总结
个人感觉,用 sqlcommand比较灵活,而dataset是实现ado.net断开式连接的核心,比较安全