C# 连接数据库

csharp

浏览数:174

2019-1-7

AD:资源代下载服务
public class DBUtility_MSSQL
    {
        private static string connectionStr = @"server=109.118.1.32\Webportal;database=FIFA;uid=Dev_user03;pwd=dev03@user7g;";

        private static SqlConnection conn()
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServerConn"].ConnectionString);//connectionStr);
        }

        private static SqlConnection conn(string connectionStr)
        {
            return new SqlConnection(connectionStr);
        }
        /// <summary>
        /// 匹配数据库数据类型
        /// </summary>
        /// <param name="theType"></param>
        /// <returns></returns>
        public static SqlDbType GetDBType(System.Type theType)
        {
            System.Data.SqlClient.SqlParameter p1;
            System.ComponentModel.TypeConverter tc;
            p1 = new System.Data.SqlClient.SqlParameter();
            tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);
            if (tc.CanConvertFrom(theType))
            {
                p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
            }
            else
            {
                //Try brute force
                try
                {
                    p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
                }
                catch (Exception)
                {
                    //Do Nothing; will return NVarChar as default
                }
            }

            //if (/*p1.SqlDbType == SqlDbType.Date ||*/ p1.SqlDbType == SqlDbType.DateTime || p1.SqlDbType == SqlDbType.Float || p1.SqlDbType == SqlDbType.Decimal)
            //{
            //    p1.SqlDbType = SqlDbType.NVarChar;
            //}

            //if (p1.SqlDbType == SqlDbType.Float)
            //{
            //    p1.SqlDbType = SqlDbType.Decimal;
            //}
            return p1.SqlDbType;
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        public static void ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection sqldbconn = conn())
            {
                sqldbconn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, sqldbconn))
                {
                    if (parameters != null)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet Query2(string sql,string connstr, params SqlParameter[] parameters)
        {
            using (SqlConnection sqldbconn = conn(connstr))
            {
                sqldbconn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = sqldbconn;
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 120;

                    if (parameters != null)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }

                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adp.Fill(ds);

                    return ds;
                }
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet Query(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection sqldbconn = conn())
            {
                sqldbconn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = sqldbconn;
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 120;

                    if (parameters != null)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }

                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adp.Fill(ds);

                    return ds;
                }
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet Query(string sql, string tablename, params SqlParameter[] parameters)
        {
            using (SqlConnection sqldbconn = conn())
            {
                sqldbconn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = sqldbconn;
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 120;

                    if (parameters != null)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }

                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet(tablename);
                    adp.Fill(ds);

                    return ds;
                }
            }
        }

        /// <summary>
        /// 初始化SqlParameter
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public static SqlParameter[] getSqlParameter(DataTable dt, int i)
        {
            SqlParameter[] parameters = new SqlParameter[dt.Columns.Count];
            DataRow dr = dt.Rows[i];
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                string sColumnName = dt.Columns[j].ColumnName;
                Type tp = dt.Columns[j].DataType;
                object sValue = dr[sColumnName];
                parameters[j] = new SqlParameter("@" + sColumnName, DBUtility_MSSQL.GetDBType(tp));
                parameters[j].Value = sValue;
            }
            return parameters;
        }

       /// <summary>
        /// QueryProc
       /// </summary>
       /// <param name="sql"></param>
       /// <param name="parameters"></param>
       /// <returns></returns>
        public static DataSet QueryProc(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection sqldbconn = conn())
            {
                sqldbconn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = sqldbconn;
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.StoredProcedure;

                    if (parameters != null)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }

                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adp.Fill(ds);

                    return ds;
                }
            }
        }

        /// <summary>
        /// Exists
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static bool Exists(string sql, params SqlParameter[] parameter)
        {
            object result = ExecuteScalar(sql, parameter);
            if (result == null || result.ToString().Length == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameter)
        {
            using (SqlConnection sqldbconn = conn())
            {
                sqldbconn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = sqldbconn;
                    cmd.CommandText = sql;

                    if (parameter != null)
                    {
                        foreach (SqlParameter para in parameter)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }

                    return cmd.ExecuteScalar();
                }
            }
        }

        public static void ExecuteNonQueryProc(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection oledbconn = conn())
            {
                oledbconn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, oledbconn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    cmd.ExecuteNonQuery();
                }
            }
        }    }