C database operation summary
- 2020-05-07 20:19:40
- OfStack
1, common T-Sql statement
query: SELECT * FROM tb_test WHERE ID='1' AND name='xia'
SELECT * FROM tb_test
insert: INSERT INTO tb_test VALUES('xia','123')
INSERT INTO tb_test(name) VALUES('xia')
update: UPDATE tb_test SET password='234' WHERE ID='1'
delete: DELETE FROM tb_test WHERE ID='1'
DELETE tb_test WHERE ID='1'
Get the database connection string in vs2010
string connectionString = Properties.Settings.Default.DatabaseTestConnectionString;
3, SqlCommand type
insert, modify, delete:
4, DataTable type, query, add, modify, delete
DataTable class is used when querying, adding, deleting, and modifying DataTable
string selectStr = "SELECT * FROM tb_test2";
enquiries:
delete:
5, DataSet type
DataSet operation is basically the same as DataTabel operation, except that DataSet can store more than one table, so I will introduce more
6. Personal summary
query: SELECT * FROM tb_test WHERE ID='1' AND name='xia'
SELECT * FROM tb_test
insert: INSERT INTO tb_test VALUES('xia','123')
INSERT INTO tb_test(name) VALUES('xia')
update: UPDATE tb_test SET password='234' WHERE ID='1'
delete: DELETE FROM tb_test WHERE ID='1'
DELETE tb_test WHERE ID='1'
Get the database connection string in vs2010
string connectionString = Properties.Settings.Default.DatabaseTestConnectionString;
3, SqlCommand type
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(); // Data is read
command.Connection.Close();
}
catch (SqlException ex)
{
throw ex;
}
}
insert, modify, delete:
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 type, query, add, modify, delete
DataTable class is used when querying, adding, deleting, and modifying DataTable
string selectStr = "SELECT * FROM tb_test2";
enquiries:
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
// Data is read
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);
// Add data
DataRow newRow = dataTable.NewRow();
newRow["id"] = "tesr";
newRow["name"] = "111";
dataTable.Rows.Add(newRow);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(dataTable); // Update to database
}
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);
// Modify the data
DataRow updateRow = dataTable.Rows[0];
updateRow["id"] = "update";
updateRow["name"] = "222";
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(dataTable); // Update to database
}
catch (SqlException ex)
{
throw ex;
}
}
delete:
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataTable.Rows[0].Delete(); // Delete records
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(dataTable); // Update to database
}
catch (SqlException ex)
{
throw ex;
}
}
5, DataSet type
DataSet operation is basically the same as DataTabel operation, except that DataSet can store more than one table, so I will introduce more
6. Personal summary