C#访问数据库方法

最新推荐文章于 2024-06-20 09:39:00 发布

liusping

最新推荐文章于 2024-06-20 09:39:00 发布

阅读量605

点赞数

文章标签：数据库 c# string exception cmd object

本文链接：https://blog.csdn.net/liusping/article/details/2829715

版权

        #region 执行查询语句返回影响行数
        public static int ExecuteNonQuery(string str_cmd,out string str_error)
        {
            int i=0;
            str_error=string.Empty;
            SqlConnection conn=CreateConn();
            SqlCommand cmd=new SqlCommand(str_cmd,conn);
            try
            {
                conn.Open();
                i=cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                str_error=ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return i;
        }
        #endregion
        #region 返回第一行第一列
        public static string ExecuteScalar(string str_cmd,out string str_error)
        {
            string str_value=string.Empty;
            str_error=string.Empty;
            SqlConnection conn=CreateConn();
            SqlCommand cmd=new SqlCommand(str_cmd,conn);
            try
            {
                conn.Open();
                str_value=cmd.ExecuteScalar().ToString();
            }
            catch(Exception ex)
            {
                str_error=ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return str_value;
        }
        #endregion
        #region 返回datatbale from datareader
        public static DataTable ExecuteReader(string str_cmd,out string str_error)
        {
            DataTable dt=new DataTable();
            SqlDataReader dr=null;
             str_error=string.Empty;
            SqlConnection conn=CreateConn();
            SqlCommand cmd=new SqlCommand(str_cmd,conn);
            try
            {
                conn.Open();
                dr=cmd.ExecuteReader();
                dt=DataReaderToTable(dr);
            }
            catch(Exception ex)
            {
                str_error=ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return dt;
         }
        #region 循环SqlDataReader填入Table
        private static DataTable DataReaderToTable(SqlDataReader dr)
        {
            DataTable dt=new DataTable();
            int count=dr.FieldCount;
            int index=1;
            if(count>0)
            {
                for(int i=0;i
                {
                    dt.Columns.Add(dr.GetName(i),dr.GetFieldType(i));
                }
                dt.Columns.Add("id");//编号
                dt.BeginLoadData();
                while(dr.Read())
                {
                    object[] objectRow=new object[count+1];
                    for(int i=0;i
                    {

                        objectRow[i]=dr[dr.GetName(i)];
                    }
                    objectRow[count]=index;//编号
                    dt.LoadDataRow(objectRow,true);
                    index++;
                }
                dt.EndLoadData();
            }
            return dt;

        }
        #endregion
        public static SqlConnection CreateConn()
        {
            //string str_conn="server=.;database=bidding;user id=sa;pwd=jhc;";
            SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
            return conn;
        }