C#数据库操作小结
C#数据库操作小结
发布时间:2016-12-28 来源:查字典编辑
摘要:1、常用的T-Sql语句查询:SELECT*FROMtb_testWHEREID='1'ANDname='xia'SELECT*FROMtb...

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断开式连接的核心,比较安全

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新C#教程学习
热门C#教程学习
编程开发子分类