.NET 存储过程访问数据库

这篇文章记录了我从一开始使用.NET访问数据库的历程，从一开始最基本的直接访问数据库存储过程，到后面使用SqlSugar、Dapper、Entity Framework (EF)框架的使用，看到这篇文章的初始版本，自己都觉得有点感慨我当初的傻里傻气

存储过程创建：

ALTER procedure Data 
@SerialNumber VarChar (50),
@type VarChar (10)

as 
BEGIN
SELECT b1.LotNumber FROM m_HL100GEEPROM_T b1 WHERE SerialNumber=@SerialNumber 
NED
GO

 

C#代码：

public static DataTable SQL1(string SerialNumber, string type)
        {
            DataTable DT = new DataTable();
            string conString = "Data Source=; Initial Catalog=;User ID=;Password=";//连接数据库的字符
            string sql = "Data";//要调用的存储过程名  
            try
            {
                SqlConnection conStr = new SqlConnection(conString);//SQL数据库连接对象，以数据库链接字符串为参数  
                SqlCommand comStr = new SqlCommand(sql, conStr);//SQL语句执行对象，第一个参数是要执行的语句，第二个是数据库连接对象  
                comStr.CommandType = CommandType.StoredProcedure;//设置执行类型为存储过程  
                                                                 //依次设定存储过程的参数  
                comStr.Parameters.Add("@SerialNumber", SqlDbType.Text).Value = SerialNumber;
                comStr.Parameters.Add("@type", SqlDbType.Text).Value = type;

                conStr.Open();//打开数据库连接  
                SqlDataAdapter SqlDataAdapter1 = new SqlDataAdapter(comStr);
                SqlDataAdapter1.Fill(DT);
                conStr.Close();//关闭连接 
            }

            catch (Exception ex)
            {
                MessageBox.Show("操作不成功");
            }

            return DT;
        }

 

Dapper框架：

<connectionStrings>
    <add name="AOC" connectionString="Data Source=****; Initial Catalog=****;User ID=****;Password=****" />
  </connectionStrings>

  using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AOC"].ConnectionString))
                {
                    DynamicParameters sp = new DynamicParameters();
                    sp.Add("@Part_Number", Part_Number);
                    sp.Add("@SerialNumber", SerialNumber);
                    sp.Add("@type", type);
                    Data = connection.Query<GD>("Data", sp, commandType: CommandType.StoredProcedure).SingleOrDefault();
                }

 using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["AOC"].ConnectionString))
                {
                    var param = new DynamicParameters();
                    param.Add("@ppid1", barcode);
                    param.Add("@ppid2", custID);
                    param.Add("@stationid", stationid);   //存储过程判断条件
                    param.Add("@PutResult", "", DbType.String, ParameterDirection.Output);
                    
                    db.Execute("AOC_SpotCheck", param, null, null, CommandType.StoredProcedure);
                    PutResult = param.Get<string>("@PutResult");
                    if (PutResult == null || PutResult == "") { PutResult = "NG:数据不存在"; }

                }

 

EF框架：

    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
 <connectionStrings>
    <add name="OEE" connectionString="Data Source=; Initial Catalog=;Persist Security Info=True;User ID=;Password=" providerName="System.Data.SqlClient" />
  </connectionStrings>
 <entityFramework>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>

  List<DBRead> DBList = new List<DBRead>();  //DBRead为实体类
            using (var db = new DataContext())
            {
                SqlParameter type = new SqlParameter("@type", DBparam.type);
                SqlParameter equipmentID = new SqlParameter("@equipmentID", DBparam.Testpcid);
                SqlParameter CollecttimeA = new SqlParameter("@CollecttimeA", DBparam.CollecttimeA);
                SqlParameter CollecttimeB = new SqlParameter("@CollecttimeB", DBparam.CollecttimeB);
                DBList = db.Database.SqlQuery<DBRead>("exec [OEEReport] @type,@equipmentID,@CollecttimeA,@CollecttimeB", type, equipmentID, CollecttimeA, CollecttimeB).Cast<DBRead>().ToList();
                db.SaveChanges();
            }

  public class DataContext : DbContext
    {
        public DataContext() : base("OEE") { }//指定连接字符串

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();  // 移除表名公约  
       

        }
        public void FixEfProviderServicesProblem()
        {
            var instance = System.Data.Entity.SqlServer.SqlProviderServices.Instance;
        }


    }