dataaccess.cs

主要用语管理系统中的sql数据库备份和数据库恢复,可以完全备份和差异备份

using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Xml;
using System.Windows.Forms;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Text;
using DataManager.Properties;

namespace DataManager
{
	/// <summary>
	/// </summary>
	public class DataAccess
	{
		
		public DataAccess()
		{

		}	
		/// <summary>
		/// 配置数据库连接字符串
		/// </summary>
        public static string ConnectionString = Settings.Default.ConnectionString.ToString();

        public SqlConnection myConn;
        public SqlCommand myCommand;
        public SqlDataAdapter myAda;
        public SqlDataReader myReader;
        public DataSet ds;
        public DataTable dt;
        public DataRow dr;
        public string FileName;
        public int FileSize;

        public string UserName;
        public string TrueName;
        public string Dept_Name;
        public string Dept_Id;
        //**********************************>> 数据库操作函数 <<**********************************//
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void open()
        {
            myConn = new SqlConnection(ConnectionString);
            myConn.Open();
        }
        public SqlDataReader GetReader(string str_Sql)
        {
            open();
            myCommand = new SqlCommand(str_Sql, myConn);
            try
            {
                myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                throw;
            }
            return myReader;
        }
        /// <summary>
        /// 执行一个Sql语句返回一个DataSet对象
        /// </summary>
        /// <param name="str_Sql">sql语句</param>
        /// <returns></returns>
        public DataSet GetDs(string str_Sql)
        {
            open();
            myAda = new SqlDataAdapter(str_Sql, myConn);
            ds = new DataSet();
            myAda.Fill(ds);
            myConn.Close();
            return ds;
        }
        /// <summary>
        /// 执行一个Sql语句返回一个DataSet对象
        /// </summary>
        /// <param name="str_Sql">sql语句</param>
        /// <param name="str_Table">内寸表名称</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetDs(string str_Sql, string str_Table)
        {
            open();
            myAda = new SqlDataAdapter(str_Sql, myConn);
            ds = new DataSet();
            myAda.Fill(ds, str_Table);
            myConn.Close();
            return ds;
        }

        /// <summary>
        /// 获得包含在DataSet对象的映谢表集合中的index为0的映谢表
        /// </summary>
        /// <param name="str_Sql">打开表Sql语句</param>
        public void GetTable(string str_Sql)
        {
            dt = GetDs(str_Sql).Tables[0];
        }
        /// <summary>
        /// 通过传Sql语句关键key值获得表中一行的数据
        /// </summary>
        /// <param name="str_Sql">带关键Key值参数的Select-SQL语句</param>
        public void GetTableRow(string str_Sql)
        {
            dr = GetDs(str_Sql).Tables[0].Rows[0];
            myConn.Close();
        }

        /// <summary>
        /// 执行一个Sql语句
        /// </summary>
        /// <param name="str_Sql">sql语句</param>
        public void RunSql(string str_Sql)
        {
            open();
            myCommand = new SqlCommand(str_Sql, myConn);
            myCommand.ExecuteNonQuery();
            myConn.Close();
        }
        /// <summary>
        /// 获得记录总数
        /// </summary>
        /// <param name="str_SqlAndWhere">该参数可以是数据库表名称或者数据库表名+where条件</param>
        /// <returns>int型记录总数</returns>
        public int GetRsCount(string str_SqlAndWhere)
        {
            string strSql;
            int Count;
            open();
            strSql = "select count(*) from " + str_SqlAndWhere;
            SqlCommand cmd = new SqlCommand(strSql, myConn);
            Count = (int)cmd.ExecuteScalar();
            return Count;
        }
        /// <summary>
        /// 获得数据库表key值最大值
        /// </summary>
        /// <param name="str_Id">key字段名称</param>
        /// <param name="str_table">数据库表</param>
        /// <returns>返回int型（表中最大id号）</returns>
        public int GetMaxId(string str_Id, string str_table)
        {
            int intMaxId;
            string strSql;
            open();
            strSql = "select Max(" + str_Id + ") from " + str_table;
            SqlCommand cmd = new SqlCommand(strSql, myConn);
            strSql = cmd.ExecuteScalar().ToString();
            if (strSql == "")
            {
                intMaxId = 0;
            }
            else
            {
                intMaxId = int.Parse(strSql);
            }

            return intMaxId;
        }
        /// <summary>
        /// 获得int_Count随机数
        /// </summary>
        /// <param name="int_Count">位数</param>
        /// <returns></returns>
        public string GetRandom(int int_Count)
        {
            string str_RV = "1";
            for (int i = 0; i < int_Count - 2; i++)
            {
                str_RV = str_RV + "0";
            }
            Random r = new Random();
            str_RV = r.Next(int.Parse(str_RV)).ToString();
            int int_Count1 = int_Count - str_RV.Length;
            if (int_Count1 > 0)
            {
                for (int i = 0; i < int_Count1; i++)
                {
                    str_RV = str_RV + "0";
                }
            }
            return str_RV;
        }
        /// <summary>
        /// 获得一个14+int_Count位时间随机数
        /// </summary>
        /// <param name="int_Count">随机位数</param>
        /// <returns></returns>
        public string GetDateRandom(int int_Count)
        {
            string strData = DateTime.Now.ToString("MMddhhmmss");
            strData = strData.Replace(":", "");
            strData = strData.Replace("-", "");
            strData = strData.Replace(" ", "");
            strData = strData + GetRandom(int_Count);
            return strData;
        }
        /// <summary>
        /// 通过SqlCommandBuilder对象增加数据库记录
        /// </summary>
        /// <param name="str_Sql">Select-SQL语句</param>
        public void Builder(string str_Sql)
        {
            open();
            myAda = new SqlDataAdapter(str_Sql, myConn);
            SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAda);
            ds = new DataSet();
            myAda.Fill(ds);
            dr = ds.Tables[0].NewRow();
        }
        /// <summary>
        /// 关闭数据库的添加操作
        /// </summary>
        public void BuilderClose()
        {
            ds.Tables[0].Rows.Add(dr);
            myAda.Update(ds);
            myConn.Close();
            ds.Clear();
        }
        /// <summary>
        /// 数据库的更新操作
        /// </summary>
        /// <param name="str_Sql">Select-SQL语句</param>
        public void BuilderEdit(string str_Sql)
        {
            open();
            myAda = new SqlDataAdapter(str_Sql, myConn);
            SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAda);
            ds = new DataSet();
            myAda.Fill(ds);
            dr = ds.Tables[0].Rows[0];
        }
        /// <summary>
        /// 关闭数据库的更新操作
        /// </summary>
        public void BuilderEditClose()
        {
            myAda.Update(ds);
            myConn.Close();
            ds.Clear();
        }
        /// <summary>
        /// 关闭所有SqlConnection对象和DataSet对象
        /// </summary>
        public void close()
        {
            if (myConn != null)
            {
                myConn.Close();
            }
            if (ds != null)
            {
                ds.Clear();
            }
        }

        //---------------------------------dataGridGird控件---------------------------
        /// <summary>
        /// 绑定dataGridGird控件
        /// </summary>
        /// <param name="str_Sql">sql语句</param>
        /// <param name="dgd">dataGridGird控件的id</param>
        public void BindDataGrid(string str_Sql, DataGridView dgd)
        {
            GetTable(str_Sql);
            dgd.DataSource = dt.DefaultView;
            dgd.EndEdit();
        }


        /// <summary>
        /// 获得某个字符串在另个字符串最后一次出现时后面所有字符
        /// </summary>
        /// <param name="strOriginal">要处理字符</param>
        /// <param name="strSymbol">符号</param>
        /// <returns>返回值</returns>
        public string GetLastStr(string strOriginal, string strSymbol)
        {
            int strPlace = strOriginal.LastIndexOf(strSymbol) + strSymbol.Length;
            strOriginal = strOriginal.Substring(strPlace);
            return strOriginal;
        }
        /// <summary>
        /// 获得某个字符串在另个字符串第一次出现时前面所有字符
        /// </summary>
        /// <param name="strOriginal">要处理字符</param>
        /// <param name="strSymbol">符号</param>
        /// <returns>返回值</returns>
        public string GetFirstStr(string strOriginal, string strSymbol)
        {
            int strPlace = strOriginal.IndexOf(strSymbol);
            if (strPlace != -1)
                strOriginal = strOriginal.Substring(0, strPlace);
            return strOriginal;
        }
        /// <summary>
        /// 获得单个字段值
        /// </summary>
        /// <param name="str_Sql">Sql语句</param>
        /// <returns></returns>
        public string GetFiledValue(string str_Sql)
        {
            string str;
            open();
            SqlCommand cmd = new SqlCommand(str_Sql,myConn);
            object obj = cmd.ExecuteScalar();
            if (obj == System.DBNull.Value)
            {
                str = "";
            }
            else
            {