ado.net 练习

m0_50985216

已于 2024-09-13 10:16:33 修改

阅读量185

点赞数 2

分类专栏： Ado.Net 文章标签： .net 服务器

于 2024-07-30 16:07:51 首次发布

本文链接：https://blog.csdn.net/m0_50985216/article/details/140791473

版权

Ado.Net 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、基础

1、编写代码来执行一个SELECT语句，并使用DataReader读取数据。

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";
        string query = "SELECT * FROM YourTable";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine($"{reader["ColumnName1"]}, {reader["ColumnName2"]}");
            }

            reader.Close();
        }
    }
}

2、编写代码来执行一个SELECT语句，并将结果填充到DataSet中。

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";
        string query = "SELECT * FROM YourTable";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "YourTable");//YourTable是别名

            foreach (DataRow row in dataSet.Tables["YourTable"].Rows)
            {
                Console.WriteLine($"{row["ColumnName1"]}, {row["ColumnName2"]}");
            }
        }
    }
}

3、如何使用参数化查询来防止SQL注入？请提供示例代码。

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";
        string query = "SELECT * FROM YourTable WHERE ColumnName = @value";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@value", "some_value");

            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine($"{reader["ColumnName1"]}, {reader["ColumnName2"]}");
            }

            reader.Close();
        }
    }
}

4、编写代码来插入一条记录到数据库中。

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";
        string query = "INSERT INTO YourTable (ColumnName1, ColumnName2) VALUES (@value1, @value2)";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@value1", "value1");
            command.Parameters.AddWithValue("@value2", "value2");

            connection.Open();
            int result = command.ExecuteNonQuery();

            Console.WriteLine($"Rows affected: {result}");
        }
    }
}

5、如何使用事务来确保数据操作的一致性

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // 1. 创建连接字符串
        string connectionString = "your_connection_string_here";

        // 2. 使用连接字符串创建SqlConnection对象
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // 3. 打开连接
            connection.Open();

            // 4. 开始事务
            SqlTransaction transaction = connection.BeginTransaction();

            try
            {
                // 5. 使用SQL命令、SqlConnection对象和事务对象来创建SqlCommand对象
                SqlCommand command1 = new SqlCommand("INSERT INTO YourTable (ColumnName) VALUES (@value1)", connection, transaction);
                command1.Parameters.AddWithValue("@value1", "value1");
                
                SqlCommand command2 = new SqlCommand("INSERT INTO YourTable (ColumnName) VALUES (@value2)", connection, transaction);
                command2.Parameters.AddWithValue("@value2", "value2");

                // 6. 执行SQL命令
                command1.ExecuteNonQuery();
                command2.ExecuteNonQuery();

                // 7. 提交事务
                transaction.Commit();
                Console.WriteLine("Both records are written to the database.");
            }
            catch (Exception ex)
            {
                // 7. 回滚事务
                transaction.Rollback();
                Console.WriteLine("An error occurred. Transaction rolled back.");
                Console.WriteLine(ex.Message);
            }
            finally
            {
                // 8. 关闭连接
                connection.Close();
            }
        }
    }
}

6、如果需要在 DataSet 中填充多个表，并且使用指定的表名：

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // 1. 创建连接字符串
        string connectionString = "your_connection_string_here";
        
        // 2. 创建多个SQL查询
        string query1 = "SELECT * FROM Table1";
        string query2 = "SELECT * FROM Table2";

        // 3. 使用连接字符串创建SqlConnection对象
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // 4. 创建SqlDataAdapter对象
            SqlDataAdapter adapter1 = new SqlDataAdapter(query1, connection);
            SqlDataAdapter adapter2 = new SqlDataAdapter(query2, connection);

            // 5. 创建DataSet对象来存储查询结果
            DataSet dataSet = new DataSet();

            // 6. 使用SqlDataAdapter的Fill方法将查询结果填充到DataSet中，并指定表名
            adapter1.Fill(dataSet, "Table1");
            adapter2.Fill(dataSet, "Table2");

            // 7. 遍历DataSet中的数据，使用指定的表名访问数据
            // 第一个表的数据
            Console.WriteLine("Table1 data:");
            foreach (DataRow row in dataSet.Tables["Table1"].Rows)
            {
                Console.WriteLine($"{row["ColumnName1"]}, {row["ColumnName2"]}");
            }

            // 第二个表的数据
            Console.WriteLine("Table2 data:");
            foreach (DataRow row in dataSet.Tables["Table2"].Rows)
            {
                Console.WriteLine($"{row["ColumnName1"]}, {row["ColumnName2"]}");
            }
        }
    }
}

7、使用事务确保数据一致性

using System;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void PerformTransaction()
    {
        string connectionString = "your_connection_string_here";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();
            
            SqlCommand command1 = new SqlCommand("INSERT INTO YourTable (ColumnName) VALUES (@Value1)", connection, transaction);
            command1.Parameters.AddWithValue("@Value1", "value1");

            SqlCommand command2 = new SqlCommand("UPDATE YourTable SET ColumnName = @Value2 WHERE Id = @Id", connection, transaction);
            command2.Parameters.AddWithValue("@Value2", "value2");
            command2.Parameters.AddWithValue("@Id", 1);

            try
            {
                command1.ExecuteNonQuery();
                command2.ExecuteNonQuery();
                
                transaction.Commit();
                Console.WriteLine("Transaction committed.");
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                Console.WriteLine("Transaction rolled back.");
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

8、使用存储过程

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void CallStoredProcedure(int id, string newValue)
    {
        string connectionString = "your_connection_string_here";
        string procedureName = "YourStoredProcedure";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(procedureName, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@NewValue", newValue);

            try
            {
                connection.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("Stored procedure executed.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

9、使用DataSet和DataAdapter更新数据

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void UpdateDataSet()
    {
        string connectionString = "your_connection_string_here";
        string selectQuery = "SELECT * FROM YourTable";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            DataSet dataSet = new DataSet();

            try
            {
                connection.Open();
                adapter.Fill(dataSet, "YourTable");

                DataTable table = dataSet.Tables["YourTable"];
                DataRow row = table.Rows[0];
                row["ColumnName"] = "NewValue";

                adapter.Update(dataSet, "YourTable");
                Console.WriteLine("DataSet updated.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

10、异步数据库操作

using System;
using System.Data.SqlClient;
using System.Threading.Tasks;

public class DatabaseOperations
{
    public async Task ExecuteQueryAsync()
    {
        string connectionString = "your_connection_string_here";
        string query = "SELECT * FROM YourTable";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);

            try
            {
                await connection.OpenAsync();
                SqlDataReader reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    Console.WriteLine($"{reader["ColumnName1"]}, {reader["ColumnName2"]}");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

11、配置和使用命令超时

using System;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void SetCommandTimeout()
    {
        string connectionString = "your_connection_string_here";
        string query = "WAITFOR DELAY '00:00:05'; SELECT * FROM YourTable"; // Simulate long-running query

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            command.CommandTimeout = 2; // Set timeout to 2 seconds

            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine($"{reader["ColumnName1"]}, {reader["ColumnName2"]}");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

12、执行复杂查询并使用嵌套结果集

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void ExecuteComplexQuery()
    {
        string connectionString = "your_connection_string_here";
        string query = "SELECT * FROM YourTable1; SELECT * FROM YourTable2";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);

            try
            {
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                DataTable table1 = dataSet.Tables[0];
                DataTable table2 = dataSet.Tables[1];

                Console.WriteLine("Table1 data:");
                foreach (DataRow row in table1.Rows)
                {
                    Console.WriteLine($"{row["ColumnName1"]}, {row["ColumnName2"]}");
                }

                Console.WriteLine("Table2 data:");
                foreach (DataRow row in table2.Rows)
                {
                    Console.WriteLine($"{row["ColumnName1"]}, {row["ColumnName2"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

13、ADO.NET 写一个调用存储过程的代码片段，并传递参数给存储过程。

string strConn = @"server=KSLZ28OF4793\MSSQLSERVER1;database=demo1;trusted_connection=true;TrustServerCertificate=true";
            string sql = "select * from book";
            //存储过程p_test1有一个输入参数和一个输出参数
            using (SqlConnection conn = new(strConn))
            {
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand("p_test1", conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.AddWithValue("@a1", "kawa8");

                SqlParameter sqlParameter = new SqlParameter();
                sqlParameter.Direction = ParameterDirection.Output;
                sqlParameter.ParameterName = "@a2";
                sqlParameter.Size = 20;//要指定大小
                sqlParameter.SqlDbType = SqlDbType.VarChar;
                sqlCommand.Parameters.Add(sqlParameter);


                int a = sqlCommand.ExecuteNonQuery();
                Console.WriteLine(a);
                Console.WriteLine(sqlParameter.Value);

                conn.Close();
            }

二、批量操作

1、批量插入数据：编写一个方法，使用 SqlBulkCopy 类将一个 DataTable 中的数据批量插入到 SQL Server 数据库中。

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void BulkInsert(DataTable dataTable)
    {
        string connectionString = "your_connection_string_here";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = "YourTable";
                
                try
                {
                    bulkCopy.WriteToServer(dataTable);
                    Console.WriteLine("Bulk insert completed.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                }
            }
        }
    }
}

2、批量更新数据：编写一个方法，使用 DataAdapter 和 SqlCommandBuilder 进行批量更新。创建一个 DataSet，修改其中的数据，并将修改后的数据批量更新到数据库中。

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void BatchUpdate(DataSet dataSet)
    {
        string connectionString = "your_connection_string_here";
        string selectQuery = "SELECT * FROM YourTable";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            try
            {
                connection.Open();
                adapter.Fill(dataSet, "YourTable");

                DataTable table = dataSet.Tables["YourTable"];
                foreach (DataRow row in table.Rows)
                {
                    if (row.RowState == DataRowState.Modified)
                    {
                        row["ColumnName"] = "UpdatedValue"; // Update as needed
                    }
                }

                adapter.Update(dataSet, "YourTable");
                Console.WriteLine("Batch update completed.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

3、批量删除数据：编写一个方法，使用 DataAdapter 批量删除 DataSet 中的数据。首先填充 DataSet，然后删除其中的记录，并将这些删除操作批量应用到数据库中。

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void BatchDelete(DataSet dataSet)
    {
        string connectionString = "your_connection_string_here";
        string selectQuery = "SELECT * FROM YourTable";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            try
            {
                connection.Open();
                adapter.Fill(dataSet, "YourTable");

                DataTable table = dataSet.Tables["YourTable"];
                DataRow[] rowsToDelete = table.Select("ConditionColumn = 'Value'"); // Example condition

                foreach (DataRow row in rowsToDelete)
                {
                    row.Delete();
                }

                adapter.Update(dataSet, "YourTable");
                Console.WriteLine("Batch delete completed.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

4、批量更新带有条件的数据

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void BatchUpdateWithCondition(DataSet dataSet)
    {
        string connectionString = "your_connection_string_here";
        string selectQuery = "SELECT * FROM YourTable";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            try
            {
                connection.Open();
                adapter.Fill(dataSet, "YourTable");

                DataTable table = dataSet.Tables["YourTable"];
                foreach (DataRow row in table.Rows)
                {
                    if (row["ConditionColumn"].ToString() == "Value")
                    {
                        row["ColumnName"] = "UpdatedValue";
                    }
                }

                adapter.Update(dataSet, "YourTable");
                Console.WriteLine("Batch update with condition completed.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

5、使用事务进行批量操作

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void BulkInsertWithTransaction(DataTable dataTable)
    {
        string connectionString = "your_connection_string_here";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();

            try
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))
                {
                    bulkCopy.DestinationTableName = "YourTableName";
                    bulkCopy.WriteToServer(dataTable);
                }

                transaction.Commit();
                Console.WriteLine("Bulk insert completed with transaction.");
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

6、批量导出数据到文件

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class DatabaseOperations
{
    public void ExportDataToCsv()
    {
        string connectionString = "your_connection_string_here";
        string query = "SELECT * FROM YourTable";
        string csvFilePath = "data.csv";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable dataTable = new DataTable();

            try
            {
                adapter.Fill(dataTable);

                using (StreamWriter writer = new StreamWriter(csvFilePath))
                {
                    // Write column headers
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        writer.Write(dataTable.Columns[i]);
                        if (i < dataTable.Columns.Count - 1)
                            writer.Write(",");
                    }
                    writer.WriteLine();

                    // Write rows
                    foreach (DataRow row in dataTable.Rows)
                    {
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            writer.Write(row[i].ToString());
                            if (i < dataTable.Columns.Count - 1)
                                writer.Write(",");
                        }
                        writer.WriteLine();
                    }
                }
                Console.WriteLine("Data exported to CSV.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

7、批量合并数据

using System;
using System.Data;
using System.Data.SqlClient;

public class DatabaseOperations
{
    public void MergeDataFromMultipleSources(DataTable source1, DataTable source2)
    {
        string connectionString = "your_connection_string_here";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = "YourTargetTableName";

                try
                {
                    // Combine both source DataTables
                    DataTable mergedTable = source1.Copy();
                    mergedTable.Merge(source2);

                    bulkCopy.WriteToServer(mergedTable);
                    Console.WriteLine("Data merged and inserted.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                }
            }
        }
    }
}

8、

            string strConn = @"server=KSLZ28OF4793\MSSQLSERVER1;database=demo1;trusted_connection=true;TrustServerCertificate=true";
            string sql = "select * from book";
            using (SqlConnection conn = new(strConn))
            {
                conn.Open();
                
                DataTable dt = new DataTable();
                dt.Columns.Add("bookname", typeof(string));
                dt.Columns.Add("c_time",typeof(DateTime));
                dt.Columns.Add("studentid",typeof(int));
                dt.Rows.Add("kawa10",DateTime.Now,3);
                dt.Rows.Add("kawa11",DateTime.Now,3);

                using(SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn))
                {
                    sqlBulkCopy.DestinationTableName = "book";
                    sqlBulkCopy.WriteToServer(dt);
                }

                conn.Close();
            }