Core 项目下使用SQl语句和EF执行存储过程 

 1       public static DataTable ExeQuery(string connectionString, CommandType cmdType, string cmdText)
 2         {
 3             using (var conn = new SqlConnection(connectionString))
 4             {
 5                 var mydt = new DataTable();
 6                 conn.Open();
 7                 var cmd = conn.CreateCommand();
 8                 cmd.CommandType = cmdType;
 9                 cmd.CommandText = cmdText;
10                 var adp = new SqlDataAdapter(cmd);
11                 adp.Fill(mydt);
12                 return mydt;
13             }            
14         }
15 
16         public static async Task<DataTable> ExeQueryAsync(string connectionString, CommandType cmdType, string cmdText)
17         {
18             using (var conn = new SqlConnection(connectionString))
19             {
20                 var mydt = new DataTable();
21                 await conn.OpenAsync();
22                 var cmd = conn.CreateCommand();
23                 cmd.CommandType = cmdType;
24                 cmd.CommandText = cmdText;
25                 var adp = new SqlDataAdapter(cmd);
26                 adp.Fill(mydt);
27                 return mydt;
28             }
29         }
 1  private string GetId(string LOCATION, string OPERATING_UNIT)
 2         {
 3             using (var db = new OMSData.ACC_MINIEHUBContext())
 4             {
 5                 string cmd = string.Format("select ww from wTES where LOCATION = '{0}' and OIT = '{1}'", LOCATION, OPERATING_UNIT);
 6                 var mytb = fn.fnSQL.ExeQuery(db.ACC_Context.ConnectionString, CommandType.Text, cmd);
 7                 var CUST_ACCOUNT_ID = mytb.Rows[0]["CUST_ID"].ToString();
 8                 return CUST_ACCOUNT_ID;
 9             }
10         }
1   cmd = string.Format("select DeptName from [V_ACC_USER] where EMPID = '{0}'", WorkId);
2                 mytb = Asus.fn.fnSQL.ExeQuery(db.Context.ConnectionString, CommandType.Text, cmd);
3                 string DeptName = "Err";
4                 if (mytb.Rows.Count > 0)
5                 {
6                     DeptName = mytb.Rows[0]["Name"].ToString();
7                     DeptName = DeptName.Substring(DeptName.LastIndexOf('-') + 1);
8                 }

 

 SqlParameter[] paras = new SqlParameter[] {
                  new SqlParameter("@TRIP_NUMBER",hubTripNumber.Body.tripnumber),
                  new SqlParameter("@LoginID","bob_zhu@asus.com")
                };
 var s = _hubdata.Database.ExecuteSqlCommand("[dbo].[SFExpressSuDaByTRIP_NUMBER] @TRIP_NUMBER,@LoginID", paras);

 

 

 public class DBHelper
    {
        public static string ConnectionString { get; set; }
        public async Task<InvoiceUrl> GetAttachUrl(string OrderSN)
        {
            string s = "select * from xxxx where DDH = @ordersn order by createdate desc";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                var q = await conn.QueryAsync<InvoiceUrl>(s, new { ordersn = OrderSN });
                return q.ToList().FirstOrDefault();
            }
        }
    }

 

posted @ 2019-05-30 11:28  ProZkb  阅读(325)  评论(0)  编辑  收藏  举报