C#中怎样连接数据库并将查询结果转为实体类以及如何加入事务

冷不防 2023-10-08 10:32 1阅读 0赞

## 场景 ##

新建一个程序，需要对数据的表进行查询并将查询结果转换为实体类，然后将多个实体类

再插入到另一个数据库的表中，执行插入的过程中要使用事务。

注：

博客主页：  
[https://blog.csdn.net/badao\_liumang\_qizhi][https_blog.csdn.net_badao_liumang_qizhi]  
关注公众号  
霸道的程序猿  
获取编程相关电子书、教程推送与免费下载。

## 实现 ##

### 不带事务只是查询 ###

//储存数据的工具初始化
                DataSet idxDs = new DataSet();
                //constr：数据库连接字符串配置
               stringconstr="server=localhost;database=Badao;uid=sa;pwd=123";
                using (SqlConnection conn=new SqlConnection(constr))
               
                {
      
                    conn.Open();
                    Console.WriteLine("开始查询索引数据...");
                    //查询索引数据
                    string idxSql = "SELECT * FROM Idx1_1";//获取sql语句
                    SqlDataAdapter idxSda = new SqlDataAdapter(idxSql, conn);   //(查询语句和连接工具)
                    idxSda.Fill(idxDs);    //将适配器数据存入DataSet工具中
                 }

注：

首先声明一个DataSet用来存储执行查询的结果，然后使用连接数据的字符串打开连接。

然后使用Adapter执行sql语句，将查询结果填充到Dataset中。

### 怎样将查询结果与实体类对应赋值 ###

IdxRecord idx = null;
                    Console.WriteLine("开始储存索引数据...");
                    foreach (DataRow row in idxDs.Tables[0].Rows)
                    {
                        idx = new IdxRecord();
                        idx.IdxID = DataProcessor.RowValue(row, "Idx_ID", 0);
                        idx.DataPoint = DataProcessor.RowValue(row, "Data_Point", 0);
                        idx.ScheduleIndex = DataProcessor.RowValue(row, "Schedule_Index", 0L);                 
                        idxList.Add(idx);
                    }

注：

声明一个实体类，其中要有与数据库列所对应的字段。

然后将DataSet中的内容与实体列的属性一一赋值。

最后将实体类对象添加到实体类的list上。

其中DataProcessor.RowValue是一个工具类中的方法，此方法中的第二个参数是对应的数据库中的列

public static short RowValue(DataRow dr, string field, short defaultValue)
            {
                short Result = defaultValue;
                if (dr.Table.Columns.Contains(field))
                {
                    if (dr[field] != null && dr[field] != DBNull.Value)
                    {
                        if (short.TryParse(dr[field].ToString(), out Result))
                        {
                            return Result;
                        }
                    }
                }
                else
                {
                    Console.WriteLine("DataTable中不存在[" + field + "]列!");
                }
                return defaultValue;
            }

### 怎样开启事务并存入数据 ###

//存入bak数据库
               stringconstrBak="server=localhost;database=BadaoBak;uid=sa;pwd=123";
                using (SqlConnection conn = new SqlConnection(constrBak))//constr：数据库连接配置
                {
                    conn.Open();
                    //开启事务
                    SqlTransaction trans = conn.BeginTransaction();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;//添加连接工具
                    cmd.Transaction = trans;//添加事务
                    try
                    {
                        cmd.CommandText = "INSERT INTO idx1_1 values  ('" + idx.IdxID + "','" + idx.StepEnd +"')";//添加sql语句
                        cmd.ExecuteNonQuery();//执行
                        Console.WriteLine("插入索引数据成功");
                        trans.Commit();//执行完成之后提交
                       
    
                    }
                    catch (Exception e)
                    {
                        //执行sql语句失败，事务回滚
                        trans.Rollback();
                      
    
                    }
                    finally
                    {
                        conn.Close();
    
                    }
                }

### 完整示例代码 ###

public static void Main(string[] args)
            {
                List<IdxRecord> idxList = null;         //索引数据
                //储存数据的工具初始化
                DataSet idxDs = new DataSet();
                //constr：数据库连接字符串配置
                string constr = "server=localhost;database=Badao;uid=sa;pwd=123";
                using (SqlConnection conn=new SqlConnection(constr))
                {
                    conn.Open();
                    Console.WriteLine("开始查询索引数据...");
                    //查询索引数据
                    string idxSql = "SELECT * FROM Idx1_1";//获取sql语句
                    SqlDataAdapter idxSda = new SqlDataAdapter(idxSql, conn);   //(查询语句和连接工具)
                    idxSda.Fill(idxDs);    //将适配器数据存入DataSet工具中
                    idxList = new List<IdxRecord>();
                    IdxRecord idx = null;
                    Console.WriteLine("开始储存索引数据...");
                    foreach (DataRow row in idxDs.Tables[0].Rows)
                    {
                        idx = new IdxRecord();
                        idx.IdxID = DataProcessor.RowValue(row, "Idx_ID", 0);
                      
                        idxList.Add(idx);
                    }
                    Console.WriteLine("储存索引数据成功，成功储存数量为：" + idxList.Count);
                    Console.WriteLine("查询索引数据成功");
                    Console.WriteLine("开始根据索引数据查询记录数据...");
                    //在循环中根据索引数据查询记录数据
                    for (int i = 0; i+1 < idxList.Count;i++)
                    {
                        List<Record> recordList = new List<Record>();
                        List<List<AuxRecord>> autxRecordsList = new List<List<AuxRecord>>();
                        for (int k = idxList[i].DataPoint; k < idxList[i + 1].DataPoint;k++ )
                        {
                            DataSet recordsDs = new DataSet();
                            DataSet auxTDs = new DataSet();
                            //查询 记录数据
                            string recordSql = "SELECT * FROM WsC1_1 where Data_Point =" + k;//获取sql语句
                            //Console.WriteLine("开始执行的查询语句为：" + recordSql);
                            SqlDataAdapter recordsSda = new SqlDataAdapter(recordSql, conn);   //(查询语句和连接工具)
                            recordsSda.Fill(recordsDs);    //将适配器数据存入DataSet工具中
                            Record entity = new Record();
                            DataRow row = recordsDs.Tables[0].Rows[0];
                            entity.DataPoint = DataProcessor.RowValue(row, "Data_Point", 0);
                            entity.ScheduleIndex = DataProcessor.RowValue(row, "Schedule_Index", 0L);
                           
                            recordList.Add(entity);
                            //Console.WriteLine("根据索引数据的DataPoint：" + k + "查询到的记录数据的DataPoint:" + entity.DataPoint);
    
                            //根据索引数据查询辅助通道温度数据
                            Console.WriteLine("开始根据记录数据查询辅助通道温度数据....");
                            List<AuxRecord> autxRecords = new List<AuxRecord>();         //辅助通道温度数据
                            string AuxTSql = "SELECT * FROM Aux1_1_25 where IvIndex =" + entity.AuxIndex;//获取sql语句
                            SqlDataAdapter AuxTSda = new SqlDataAdapter(AuxTSql, conn);   //(查询语句和连接工具)
                            AuxTSda.Fill(auxTDs);    //将适配器数据存入DataSet工具中
                            //autxRecords = new List<AuxRecord>();
                            AuxRecord aux = null;
                            foreach (DataRow auxrow in auxTDs.Tables[0].Rows)
                            {
                                aux = new AuxRecord();
                                aux.DataPoint = DataProcessor.RowValue(auxrow, "Data_Point", 0);
                                aux.IvIndex = DataProcessor.RowValue(auxrow, "IvIndex", 0);
                                foreach (DataColumn col in auxTDs.Tables[0].Columns)
                                {
                                    if (col.ColumnName.StartsWith("T") || col.ColumnName.StartsWith("V"))
                                    {
                                        aux.Data.Add(DataProcessor.RowValue(row, col.ColumnName, 0D));
                                    }
                                }
                                autxRecords.Add(aux);
                            }
                            autxRecordsList.Add(autxRecords);
                            Console.WriteLine("根据记录数据查询辅助通道温度数据成功");
    
                           
                        }
                        //conn.Close();
                        //开始向数据库插入中传递参数
                        bool isStoreSuccess = StoreRecordData(idxList[i],recordList,autxRecordsList);
                        if (isStoreSuccess)
                        {
                            Console.WriteLine("存入数据库成功");
                        }
                        else
                        {
                            Console.WriteLine("存入数据库失败");
                        }
                        //开始休眠
                        Console.WriteLine("开始休眠...");
                        System.Threading.Thread.Sleep(1000 * 5);//
                        Console.WriteLine("休眠结束...");
    
                    }
                   
                    //Console.WriteLine("查询辅助通道温度数据成功");
                    //Console.ReadKey();
                   
                }
            }
    
            public static bool StoreRecordData(IdxRecord idx, List<Record> recordList, List<List<AuxRecord>> autxRecordsList)
            {
                //存入bak数据库
                string constrBak = "server=localhost;database=BadaoBak;uid=sa;pwd=123";
                using (SqlConnection conn = new SqlConnection(constrBak))//constr：数据库连接配置
                {
                    conn.Open();
                    //开启事务
                    SqlTransaction trans = conn.BeginTransaction();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;//添加连接工具
                    cmd.Transaction = trans;//添加事务
                    try
                    {
                        cmd.CommandText = "INSERT INTO idx1_1 values  ('" + idx.IdxID + "','" + idx.DataPoint + "','" + idx.StepEnd +"')";//添加sql语句
                        cmd.ExecuteNonQuery();//执行
                        Console.WriteLine("插入索引数据成功");
                        foreach(Record record in recordList)
                        {
                            cmd.CommandText = "INSERT INTO WsC1_1 values  ('" + record.DataPoint + "','" + record.ScheduleIndex + "','" + record.AuxIndex + "')";//添加sql语句
                            cmd.ExecuteNonQuery();//执行
                        }
                        Console.WriteLine("插入记录数据成功");
                        foreach (List<AuxRecord> auxRecords in autxRecordsList)
                        {
                            cmd.CommandText = "INSERT INTO Aux1_1_25 values  ('" + auxRecords[0].DataPoint + "','" + auxRecords[0].IvIndex + "','" + auxRecords[0].Data[0] + "')";//添加sql语句
                            cmd.ExecuteNonQuery();//执行
                        }
                        Console.WriteLine("插入辅助通道温度数据成功");
                        trans.Commit();//执行完成之后提交
                        return true;
    
                    }
                    catch (Exception e)
                    {
                        //执行sql语句失败，事务回滚
                        trans.Rollback();
                        return false;
    
                    }
                    finally
                    {
                        conn.Close();
    
                    }
                }
            }

[https_blog.csdn.net_badao_liumang_qizhi]: https://blog.csdn.net/badao_liumang_qizhi