dbhelpersql.cs

【原创】很多情况下

            //根据结果返回布尔值
            if (intResult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region 判断是否存在--字段
        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public bool ColumnExists(string TableName, string ColumnName)
        {
            string strSQL = "select count(1) from syscolumns where [id]=object_id('" + TableName + "') and [name]='" + ColumnName + "'";
            object obj = GetSingle(strSQL);
            if (obj == null)
            {
                return false;
            }
            return Convert.ToInt32(obj) > 0;
        }
        #endregion

        #region 判断是否存在--记录
        /// <summary>
        /// 判断是否存在记录，直接采用默认连接字符串
        /// </summary>
        /// <param name="strSQL">SQL语句：SELECT</param>
        /// <returns>有记录返回true，无记录返回false</returns>
        public static bool Exists(string strSQL)
        {
            object obj = GetSingle(strSQL);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)) ||(Object.Equals(obj,0)))
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion        

        #region 将DataSet更新到指定表
        /// <summary>
        /// 将DataSet更新到指定表
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <param name="strSQL">查询语句，用于关联更新哪个表</param>
        /// <param name="strCon">数据库连接字符串</param>
        /// <returns></returns>
        public static DataSet UpdateByDataSet(DataSet ds, string strSQL, string strCon)
        {
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(strCon);
            System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn);
            System.Data.SqlClient.SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
            try
            {
                conn.Open();
                adapter.Update(ds, ds.Tables[0].TableName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ds;//数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds
        }
        /// <summary>
        /// 将DataSet更新到指定表，直接采用默认连接字符串strCon
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <param name="strSQL">查询语句，用于关联更新哪个表</param>
        /// <returns></returns>
        public static DataSet UpdateByDataSet(DataSet ds, string strSQL)
        {
            string strCon = SQLConString;
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(strCon);
            System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn);
            System.Data.SqlClient.SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
            try
            {
                conn.Open();
                adapter.Update(ds, ds.Tables[0].TableName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ds;//数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds
        }
        /// <summary>
        /// 将DataSet更新到指定表，直接采用默认连接字符串strCon,具有行提交判断功能
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <param name="strSQL">查询语句，用于关联更新哪个表</param>
        /// <returns></returns>
        public static DataSet UpdateByDataSet(string TableName, DataSet ds, string strSQL)
        {
            string strCon = SQLConString;
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(strCon);
            System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn);
            System.Data.SqlClient.SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
            try
            {
                conn.Open();
                adapter.RowUpdating += new SqlRowUpdatingEventHandler(adapter_RowUpdating);
                adapter.Update(ds, ds.Tables[0].TableName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ds;//数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds
        }
        static void adapter_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
        {
            //如果更新的表名为：T_ServiceItem
            if (e.TableMapping.DataSetTable.ToString() == "T_ServiceItem")
            {
                if (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update)
                {

                }
            }
        }
        #endregion

        #region 备份数据库
        ///   <summary>   
        ///   备份数据库 （等于0，路径错，小于0，备份失败，大于0成功）
        ///   </summary>   
        ///   <param name="DataBaseName">要备份的数据源名称</param>   
        ///   <param name="BackupToDataBase">备份到的数据库文件的路径和名称</param>   
        ///   <returns>等于0，路径错，小于0，备份失败，大于0成功</returns>   
        public static int BackUpDataBase(string DataBaseName, string BackupToDataBase)
        {
            string ProcName;//存储过程名
            string strSQL;
            int intResult = 1;

            if (!System.IO.Path.IsPathRooted(BackupToDataBase))
            {
                intResult = 0;//不是有效的文件路径
                return intResult;
            }
            
            //-------------
            using (SqlConnection Conn = new SqlConnection(SQLConString_master))//采用默认系统数据库链接字符串
            {
                try
                {
                    Conn.Open(); //打开数据库连接
                }
                catch (Exception ex)
                {
                    Conn.Close();
                    intResult = -1;
                    throw new Exception(ex.Message);
                }

                #region 删除逻辑备份设备，但不会删掉备份的数据库文件   
                ProcName = "sp_dropdevice";
                using (SqlCommand Cmd = new SqlCommand(ProcName, Conn))
                {
                    Cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter SQLPar = new SqlParameter();
                    SQLPar = Cmd.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);
                    SQLPar.Direction = ParameterDirection.Input;
                    SQLPar.Value = "Dev" + DataBaseName;

                    //如果逻辑设备不存在，略去错误   
                    try
                    {
                        Cmd.ExecuteNonQuery();
                    }
                    catch
                    {/*忽略错误*/}
                }
                #endregion

                //创建逻辑备份设备
                ProcName = "sp_addumpdevice";
                using (SqlCommand Cmd = new SqlCommand(ProcName, Conn))
                {
                    Cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter SQLPar = new SqlParameter();
                    SQLPar = Cmd.Parameters.Add("@devtype", SqlDbType.VarChar, 20);
                    SQLPar.Direction = ParameterDirection.Input;
                    SQLPar.Value = "disk";
                    //-------
                    SQLPar = Cmd.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);//逻辑设备名   
                    SQLPar.Direction = ParameterDirection.Input;
                    SQLPar.Value = "Dev" + DataBaseName;
                    //-----
                    SQLPar = Cmd.Parameters.Add("@physicalname", SqlDbType.NVarChar, 260);//物理设备名   
                    SQLPar.Direction = ParameterDirection.Input;
                    SQLPar.Value = BackupToDataBase;//备份路径及文件名

                    try
                    {
                        intResult = Cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Conn.Close();
                        intResult = -1;
                        throw new Exception(ex.Message);
                    }
                }

                //备份数据库到指定的数据库文件(完全备份)   
                strSQL = "BACKUP DATABASE " + DataBaseName + " TO " + "Dev" + DataBaseName; //+ " WITH INIT";
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    Cmd.CommandType = CommandType.Text;
                    try
                    {
                        Cmd.ExecuteNonQuery();
                        intResult = 1;//能走到这步，说明成功，给结果赋值为大于0
                    }
                    catch (Exception ex)
                    {
                        Conn.Close();
                        intResult = -1;
                        throw new Exception(ex.Message);
                    }
                }
                Conn.Close();//关闭数据库连接                  
            }
            return intResult;
        }
        #endregion

        #region 还原指定的数据库文件
        ///   <summary>   
        ///   还原指定的数据库文件  （失败小于0，成功大于等于0） 
        ///   </summary>
        ///   <param name="DataBaseName">要还原的数据库</param>   
        ///   <param name="DataBaseFile">数据库备份文件及路径</param>   
        ///   <returns>失败小于0，成功大于等于0</returns>   
        public static int RestoreDataBase(string DataBaseName, string DataBaseFile)
        {
            //杀死正在使用的数据库进程，再还原数据库

            //还原指定的数据库文件   
            string strSQL = "RESTORE DATABASE " + DataBaseName + " FROM DISK = '" + DataBaseFile + "'";
            int intResult = 0;

            using (SqlConnection Conn = new SqlConnection(SQLConString_master))//采用默认系统数据库链接字符串
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    try
                    {
                        Conn.Open();
                        Cmd.CommandType = CommandType.Text;
                        Cmd.ExecuteNonQuery();
                        intResult = 1;
                    }
                    catch (Exception ex)
                    {
                        intResult = -1;
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        Conn.Close();//关闭数据库连接   
                    }
                }
            }
            return intResult;
        }
        #endregion

        #region 获取数据库文件所在路径
        /// <summary>
        /// 获取SQL数据库文件所在路径，不存在或出错都为null
        /// </summary>
        /// <param name="DataBaseName">数据库名</param>
        /// <returns>数据文件所在路径，不存在或出错都为null</returns>
        public static string GetDataBaseFilePath(string DataBaseName)
        {
            //一般再备份前或后，记录数据源的物理路径
            string strSQL = "SELECT [filename] FROM sysdatabases WHERE name='" + DataBaseName + "'";
            return (string)GetSingle(strSQL, SQLConString_master);
        }
        #endregion

        #region 生成单号
        /// <summary>
        /// 生成单号
        /// </summary>
        /// <param name="strHear">单号的前缀</param>
        /// <param name="IDType">操作类型</param>
        /// <param name="strTableName">使用的表名</param>
        /// <param name="strFileName">单号在表中的字段名</param>
        /// <returns>返回生成的单号</returns>
        public static string CreateOperationID(string strHear, string IDType, string strTableName, string strFileName)
        {
            //模拟ID
            string CRID = "";
            string FixDay = "";

            //门店ID号后面的0表示是否操作过的反馈标识
            CRID = strHear + "0" + DateTime.Now.ToString("yyMMdd") + "00000";
            FixDay = strHear + "0" + DateTime.Now.ToString("yyMMdd");
            string FixDay1 = strHear + "1" + DateTime.Now.ToString("yyMMdd");

            string strSQL = "SELECT TOP 1 RIGHT( " + strFileName + ",LEN(" + strFileName + ") - " + FixDay.Length + ") AS MaxID" +
                "," + strFileName +
                " FROM " + strTableName + " WHERE SUBSTRING ( " + strFileName + ",1," + FixDay.Length + ") = '" + FixDay + "'" +
                " OR SUBSTRING (" + strFileName + ",1," + FixDay.Length + ") = '" + FixDay1 + "'" +
                " ORDER BY CAST ( RIGHT (" + strFileName + " ,LEN (" + strFileName + " ) - " + FixDay.Length + ") AS INT ) DESC ";

            try
            {
                int intMaxID = 0;
                object obj = GetSingle(strSQL);
                if (obj != null)
                {
                    intMaxID = Convert.ToInt32 ( obj );
                }               
                CRID = FixDay + Convert.ToInt32(intMaxID + 1).ToString("D5"); ;              
            }
            catch (Exception ee)
            {
                MessageBox.Show("生成单号遇到操作错误:\n" + ee.Message);
            }
            return CRID;
        }

        #endregion

        #endregion

    }
}