C#操作SQLServer

最新推荐文章于 2025-07-01 11:27:15 发布

原创最新推荐文章于 2025-07-01 11:27:15 发布 · 2.4k 阅读

4 ·

CC 4.0 BY-SA版权

C# 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个使用C#进行SQLServer数据库连接和操作的示例，包括连接字符串的配置、数据库连接的建立、数据查询及结果集处理，展示了如何通过SQL语句从多个表中选择并联接数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLServer连接类

public class SqlConnect
    {
        /// <summary>
        /// 连接字符串获取
        /// </summary>
        private static string connectString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnStr"].ToString();


        /// <summary>
        /// 建立数据库连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection getConn()
        {
            try
            {
                SqlConnection con = new SqlConnection(connectString);

                return con;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
       
        }
    }

接收返回值对象的Model类

public class VOSWordListView
    {
        public long Id { get; set; }

        public string UID { get; set; }

        public string ChineseName { get; set; }

        public string LargeClass { get; set; }

        public string Message { get; set; }

        public string Title { get; set; }

        public string ModelName { get; set; }

        public string Brand { get; set; }

        public string RegionName { get; set; }

        public string Province { get; set; }
    }

接收返回值对象的Model类

public List<VOSWordListView> select()
        {
            VOSWordListView v = null;
            List<VOSWordListView> vl = new List<VOSWordListView>();
            
            try
            {
                String InsertStr = @"
                                   SELECT top 10
                                    t.ID,
                                    t.ChineseName,
                                    t.LargeClass,
                                    (SELECT top 1 UID from T_VOS WHERE UID = t.UID ORDER BY UID ) as UID,
                                    (SELECT top 1 Title from T_VOS WHERE UID = t.UID ORDER BY UID ) as Title,
                                    (SELECT top 1 Message from T_VOS WHERE UID = t.UID ORDER BY UID ) as Message,
                                    (SELECT top 1 Name from T_VOS_Model_Matching WHERE UID = t.UID ORDER BY[Index] ) as ModelName,
                                    (SELECT top 1 Brand from T_VOS_Model_Matching WHERE UID = t.UID ORDER BY[Index] ) as Brand,
                                    (SELECT top 1 Name from T_VOS_Region_Matching WHERE UID = t.UID ORDER BY[Index] ) as RegionName,
                                    (SELECT top 1 Province from T_VOS_Region_Matching WHERE UID = t.UID ORDER BY[Index] ) as Province
                                    FROM T_VOS_Quality_Matching t
                                    ORDER BY t.ID DESC";


                using (var con = SqlConnect.getConn())
                {
                    con.Open();
                    SqlCommand command = new SqlCommand(InsertStr, con);
                    using (var reader = command.ExecuteReader())
                    {
                        //int clumn = reader.FieldCount;
                        while (reader.Read())
                        {
                            v = new VOSWordListView()
                            {
                                Brand = reader["Brand"].ToString(),
                                ChineseName = reader["ChineseName"].ToString(),
                                Id = Convert.ToInt64(reader["Id"]),
                                LargeClass = reader["LargeClass"].ToString(),
                                Message = reader["Message"].ToString(),
                                ModelName = reader["ModelName"].ToString(),
                                Province = reader["Province"].ToString(),
                                RegionName = reader["RegionName"].ToString(),
                                Title = reader["Title"].ToString(),
                                UID = reader["UID"].ToString()
                            };
                            vl.Add(v);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
            finally
            {

            }
            return vl;
        }

一个方法完成对数据库的操作

      public int count()
        {
            int count = 0;
            String InsertStr = @"SELECT COUNT(*)test FROM (SELECT
            t.ID,
            t.ChineseName,
            t.LargeClass,
            (SELECT top 1 UID from T_VOS WHERE UID = t.UID ORDER BY UID ) as UID,
            (SELECT top 1 Title from T_VOS WHERE UID = t.UID ORDER BY UID ) as Title,
            (SELECT top 1 Message from T_VOS WHERE UID = t.UID ORDER BY UID ) as Message,
            (SELECT top 1 Name from T_VOS_Model_Matching WHERE UID = t.UID ORDER BY[Index] ) as ModelName,
            (SELECT top 1 Brand from T_VOS_Model_Matching WHERE UID = t.UID ORDER BY[Index] ) as Brand,
            (SELECT top 1 Name from T_VOS_Region_Matching WHERE UID = t.UID ORDER BY[Index] ) as RegionName,
            (SELECT top 1 Province from T_VOS_Region_Matching WHERE UID = t.UID ORDER BY[Index] ) as Province
            FROM T_VOS_Quality_Matching t
            ) t";

            
            SqlConnection con = new SqlConnection("这里写连接字符串connectionString");

                con.Open();
                SqlCommand command = new SqlCommand(InsertStr, con);
            var reader = command.ExecuteReader();

                    while (reader.Read())
                    {

                        count = Convert.ToInt32(reader["test"]);
             
                    }
                    return count;           
        }
    }