C#中往資料庫插入空值報錯解決方案

標籤：style   color   io   os   ar   for   資料   sp   c   

C#中的NUll於SQL中的null是不一樣的, SQL中的null用C#表示出來就是DBNull.Value

 

class SqlHerper
    {
        private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        //封裝執行增刪改 只有執行insert updata delete 語句的時候返回受影響的行數，select 永遠返回的是-1
        public static int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] parameters)
        {
            using(SqlConnection conn =new SqlConnection(connStr))
            {
                using (SqlCommand com = new SqlCommand(sql, conn))
                {
                    com.CommandType = cmdType;
                    if (parameters != null)
                    {
                        foreach (SqlParameter spr in parameters)
                        {
                            if (spr.SqlValue == null)
                            {
                                spr.SqlValue = DBNull.Value;  //C#中的NUll於SQL中的null是不一樣的, SQL中的null用C#表示出來就是DBNull.Value
                            }
                            com.Parameters.Add(spr);
                        }
                        //com.Parameters.AddRange(parameters);
                    }
                    conn.Open();
                    return com.ExecuteNonQuery();
                }
            }
        }
        //封裝返回單個值的方法 只返回第一行第一列
        public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand com = new SqlCommand(sql, conn))
                {
                    com.CommandType = cmdType;
                    if (parameters != null)
                    {
                        com.Parameters.AddRange(parameters);
                    }
                    conn.Open();
                    return com.ExecuteScalar();
                }
            }
        }
        //返回SqlDataReader對象的方法
        public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connStr); //Connection 不能using 因為返回SqlDataReader對象時要保證conn串連時開啟狀態的。
            using (SqlCommand com = new SqlCommand(sql, conn))
            {
                com.CommandType = cmdType;
                if (parameters != null)
                {
                    com.Parameters.AddRange(parameters);
                }
                try
                {
                    conn.Open();
                    //CommandBehavior.CloseConnection 當關閉DataReader對象時，也自動關閉關聯的Connection對象。
                    return com.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception)
                {
                    conn.Dispose();
                    throw;
                }
            }
        }
        //返回DataTable對象的方法
        public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connStr))
            {
                adapter.SelectCommand.CommandType = cmdType;
                if (parameters != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                }
                adapter.Fill(dt);
            }
            return dt;
        }
    }

C#中往資料庫插入空值報錯解決方案