根據dudu上次指點有了下邊的sqlhepler

weixin_34162629發表於2019-01-08
SQL

 

優點是出現錯誤的時候可以返回 錯誤資訊，歡迎指點批評

sqlhepler

 class sql
    {
        private static readonly string connectionString = "server=.;uid=sa;password=sa;database=LumigentDemoDB";
            //System.Configuration.ConfigurationManager.ConnectionStrings["sqlString"].ConnectionString;

        //返回資訊
        public class DataAndInformation
        {
            //返回資訊
            public string Message { get; set; }
            //返回的任意資料 可以使dataset datatable  object 可以是影響的行數  增刪改查返回的資料都用這個
            public object obj { get; set; }
           
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="param">查詢引數</param>
        /// <param name="CommandText">儲存過程名稱或sql語句</param>
        /// <param name="CommandType">sql型別語句or儲存過程</param>
        /// <returns>DataAndInformation</returns>
        public static DataAndInformation QueryData(string CommandText, CommandType CommandType, SqlParameter[] param)
        {
            DataAndInformation di = new DataAndInformation();
            DataSet ds = new DataSet();
            SqlConnection conn=null;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = CommandText;
                cmd.CommandType = CommandType;
                cmd.Parameters.AddRange(param);//如果沒有引數就註釋掉
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(ds);
                di.obj = ds;
            }
            catch (Exception ex)
            {
                di.Message = ex.ToString();
                di.obj = null;
            }
            finally
            {
                conn.Close();
            }
            return di;
        }
    }

DLL

  　　　　　　string CommanText = "select * from Customers where custid>@custid";
            int custid = 2;
            CommandType CommandType = CommandType.Text;
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@custid",SqlDbType.Int){Value=custid}
            };
            sql.DataAndInformation di = new sql.DataAndInformation();
            di = sql.QueryData(CommanText, CommandType, param);
            DataSet ds = (DataSet)di.obj;
            string message = di.Message;
            if (message == null)
            {
                Console.WriteLine("\n執行成功\n");
                if (ds == null)
                    Console.WriteLine("\n沒有資料\n");
                else
                {
                    Console.WriteLine(ds.Tables.Count);
                    Console.WriteLine(ds.Tables[0].Rows.Count);
                }
            }
            else
            {
                Console.WriteLine("{0}:\n{1}", "執行失敗，詳細資訊：", message);
            }

 

 

 

相關文章