数据库操作类与Xml文档操作(保存/读取Connectionstring)

最新推荐文章于 2021-01-18 21:01:47 发布
最新推荐文章于 2021-01-18 21:01:47 发布
阅读量1.9k 收藏
点赞数
分类专栏： 文章收集 文章标签： 数据库 文档 xml string exception dataset
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/longcanhua/article/details/1517694
版权
这个博客介绍了一个名为DBOperation的类，该类用于数据库操作和XML文档的读写，特别是涉及连接字符串的保存和读取。类中包含了读取XML配置文件获取数据库配置信息的方法readXML，以及写入XML配置文件的方法writeXML。这两个方法都涉及到对加密后的数据进行解密和加密的操作。此外，还提供了一些数据库连接和执行SQL语句的相关方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

using System;
using System.Collections;
using System.Text;
using System.Security.Cryptography;
using System.IO;


    /// <summary>
    /// 数据库操作类与Xml文档操作(保存/读取Connectionstring)
    /// </summary>
    class DBOperation
    {
        /// <summary>
        /// 配置文件结构
        /// </summary>
        public struct ConfigStruct
        {
            /// <summary>
            /// 数据库服务器主机地址
            /// </summary>
            public string hostAddress  ;
            /// <summary>
            /// 用户名
            /// </summary>
            public string userName ;
            /// <summary>
            /// 密码
            /// </summary>
            public string password ;
            /// <summary>
            /// 数据库名
            /// </summary>
            public string DBName ;
        }


        /// <summary>
        /// 类构造函数
        /// </summary>
        public DBOperation()
        {
        //
        }


        #region 定义用于写XML文档的字段名的变量
        private string str_HA  = "hostAddress";
        private string str_UN  = "userName";
        private string str_PWD = "password";
        private string str_DBN = "DBName";
        #endregion       
       
        #region 读写Xml文档
        /// <summary>
        /// 读XML文档
        /// </summary>
        /// <param name="name">要取的配置文件中的指定数据源的名字,如:旧系统 "oldsystem" </param>
        public ConfigStruct readXML(string name)
        {  
            try
            {
                //定义新的结构变量
                ConfigStruct cfg = new ConfigStruct();

                //定义一个新的dataset
                System.Data.DataSet ds = new System.Data.DataSet();

                //判断文件是否存在,不存在提示错误并返回一个空的结构对象
                if (System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory +"config.xml"))
                {               
                    //如果存在则读取config.xml文件的数据
                    ds.ReadXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml");
                }
                else
                {
                    //                    System.Windows.Forms.MessageBox.Show("config.xml文件不存在!" , "警告",
                    //                        System.Windows.Forms.MessageBoxButtons.OK ,
                    //                        System.Windows.Forms.MessageBoxIcon.Warning);                   
                    return new ConfigStruct();
                }
               
                //判断表是否存在,不存在提示错误并返回一个空的结构对象
                if (ds.Tables.IndexOf(name.ToUpper())== -1 )
                {
                    //                    System.Windows.Forms.MessageBox.Show("在config.xml中不能找到相关的数据源的配置信息!" , "警告",
                    //                        System.Windows.Forms.MessageBoxButtons.OK ,
                    //                        System.Windows.Forms.MessageBoxIcon.Warning);                   
                    return new ConfigStruct();                   
                }

                SymmetricMethod sm = new SymmetricMethod();
               
                //存在则取其值
                cfg.hostAddress = sm.Decrypto(ds.Tables[name.ToUpper()].Select("key='" + sm.Encrypto(str_HA)   + "'")[0]["value"].ToString());
                cfg.userName    = sm.Decrypto(ds.Tables[name.ToUpper()].Select("key='" + sm.Encrypto(str_UN)   + "'")[0]["value"].ToString());
                cfg.password    = sm.Decrypto(ds.Tables[name.ToUpper()].Select("key='" + sm.Encrypto(str_PWD)  + "'")[0]["value"].ToString());
                cfg.DBName      = sm.Decrypto(ds.Tables[name.ToUpper()].Select("key='" + sm.Encrypto(str_DBN)  + "'")[0]["value"].ToString());

                ds.Dispose();

                return cfg;
            }
            catch//(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                return new ConfigStruct();
            }
        }

        /// <summary>
        /// 写XML文档
        /// </summary>
        /// <param name="name">数据源的名称</param>
        /// <param name="hostAddress">主机地址值</param>
        /// <param name="DBName">数据库名称</param>
        /// <param name="UserName">用户名</param>
        /// <param name="password">密码</param>
        /// <param name="con_str">数据库的数据源信息</param>
        public Boolean writeXML(string name , string hostAddress ,string DBName ,
            string UserName ,string password)
        {
            try
            {
                //定义一个dataset
                System.Data.DataSet   ds = new System.Data.DataSet("config");
               
                //判断是否存在config.xml文件,如果存在从该文件中读取内容到dataset
                if(System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory +"config.xml"))
                {
                    ds.ReadXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml");
                }
               
                //判断是否存在该表,如果存在则删除该表
                if(ds.Tables.IndexOf(name.ToUpper()) != -1 )
                {
                    ds.Tables.Remove(name.ToUpper());
                }

                //定义一个datatable
                System.Data.DataTable dt = new System.Data.DataTable(name.ToUpper());

                //为新定义的表增加列
                dt.Columns.Add("key");
                dt.Columns.Add("value");
               
                SymmetricMethod sm = new SymmetricMethod();

                //增加记录表新定义的表中
                dt.Rows.Add(new object[2]{ sm.Encrypto( str_HA ) , sm.Encrypto( hostAddress)});
                dt.Rows.Add(new object[2]{ sm.Encrypto( str_UN ) , sm.Encrypto( UserName)});
                dt.Rows.Add(new object[2]{ sm.Encrypto( str_PWD) , sm.Encrypto( password)});
                dt.Rows.Add(new object[2]{ sm.Encrypto( str_DBN) , sm.Encrypto( DBName)});
               
                //将表增加到定义的新的dataset中
                ds.Tables.Add(dt);

                //写入xml文档
                ds.WriteXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml");
                //释放datatable 与 dataset
                dt.Dispose();
                ds.Dispose();  
                return true;
            }
            catch//(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                return false;
            }
        }

        /// <summary>
        /// 写XML文档
        /// </summary>
        /// <param name="hostAddress">数据库服务器主机名</param>
        /// <param name="con_str">数据库的数据源信息</param>
        public void writeXML(string name , ConfigStruct con_str)
        {
            try
            {
                //定义一个dataset
                System.Data.DataSet   ds = new System.Data.DataSet("config");

                //判断是否存在config.xml文件,如果存在从该文件中读取内容到dataset
                if(System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory +"config.xml"))
                {
                    ds.ReadXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml");
                }
               
                //判断是否存在该表,如果存在则删除该表
                if(ds.Tables.IndexOf(name.ToUpper()) != -1 )
                {
                    ds.Tables.Remove(name.ToUpper());
                }

                //定义一个datatable
                System.Data.DataTable dt = new System.Data.DataTable(name.ToUpper());

                //为新定义的表增加列
                dt.Columns.Add("key");
                dt.Columns.Add("value");
               
                SymmetricMethod sm = new SymmetricMethod();

                //增加记录表新定义的表中
                dt.Rows.Add(new object[2]{ sm.Encrypto( str_HA  ),  sm.Encrypto( con_str.hostAddress)});
                dt.Rows.Add(new object[2]{ sm.Encrypto( str_UN  ),  sm.Encrypto( con_str.userName)});
                dt.Rows.Add(new object[2]{ sm.Encrypto( str_PWD ),  sm.Encrypto( con_str.password)});
                dt.Rows.Add(new object[2]{ sm.Encrypto( str_DBN ),  sm.Encrypto( con_str.DBName)});
               
                //将表增加到定义的新的dataset中
                ds.Tables.Add(dt);

                //写入xml文档
                ds.WriteXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml");
                //释放datatable 与 dataset
                dt.Dispose();
                ds.Dispose();               
            }
            catch(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                throw exp;
            }
        }

        #endregion

        #region 数据库操作类
        /// <summary>
        /// 返回连接字符串(传入多个字符串)
        /// </summary>
        /// <param name="hostAddress">数据库服务器的主机地址</param>
        /// <param name="userName">登陆的用户名</param>
        /// <param name="Password">登陆的用户密码</param>
        /// <param name="DBName">数据库名</param>
        /// <returns>String 连接字符串</returns>
        public string getConnectionString(string hostAddress , string userName ,
                                           string Password , string DBName)
        {
            try
            {
            return "Data Source="+hostAddress+";Initial Catalog="+DBName+
                    ";Persist Security Info=True;User ID="+userName+";Password="+Password+
                    ";packet size=4096";
            }
            catch//(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                return null;
            }
        }


        /// <summary>
        /// 返回连接字符串(传入一个结构对象)
        /// </summary>
        /// <param name="hostAddress">保存数据源的结构对象</param>
        /// <returns>String 连接字符串</returns>
        public string getConnectionString(ConfigStruct con_str)
        {
            try
            {
                return "Data Source="+con_str.hostAddress+";Initial Catalog="+con_str.DBName+
                    ";Persist Security Info=True;User ID="+con_str.userName+";Password="+con_str.password+
                    ";packet size=4096";
            }
            catch//(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                return null;
            }
        }

        /// <summary>
        /// 返回一个新的Connection对象,不打开连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>System.Data.SqlClient.SqlConnection</returns>
        public System.Data.SqlClient.SqlConnection connInit(string connectionString)
        {
            try
            {
                System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();               
                conn.ConnectionString = connectionString;               
                return conn;
            }
            catch //(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                return new System.Data.SqlClient.SqlConnection();
            }
        }

        /// <summary>
        /// 返回一个新的Connection对象,不打开连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>System.Data.SqlClient.SqlConnection</returns>
        public void connDispose(System.Data.SqlClient.SqlConnection sqlConn)
        {
            try
            {
                if(sqlConn.State == System.Data.ConnectionState.Open)
                {
                    sqlConn.Close();
                }
                sqlConn.Dispose();
            }
            catch //(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
            }
        }

        /// <summary>
        /// 返回一个新的Connection对象并找开该连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>System.Data.SqlClient.SqlConnection</returns>
        public System.Data.SqlClient.SqlConnection connInitAndOpen(string connectionString)
        {
            try
            {
                System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
                conn.ConnectionString = connectionString;                
                conn.Open();
                return conn;
            }
            catch //(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                return new System.Data.SqlClient.SqlConnection();
            }
        }

        /// <summary>
        /// 执行SQL语句并返回DateTable
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>System.Data.DataTable</returns>
        public System.Data.DataTable execSqlReturnDataTable(System.Data.SqlClient.SqlConnection  conn ,
                                                               string sqlString)
        {
            try
            {
                if (conn.State == System.Data.ConnectionState.Closed )
                {
                    conn.Open();
                }
                System.Data.SqlClient.SqlCommand cmd     =  new System.Data.SqlClient.SqlCommand(sqlString , conn);
                cmd.CommandTimeout = 36000 ;
                System.Data.SqlClient.SqlDataAdapter dap = new System.Data.SqlClient.SqlDataAdapter(cmd);
                System.Data.DataTable dt = new System.Data.DataTable();               
                dap.Fill(dt);               
                cmd.Dispose();
                conn.Close();
                dap.Dispose(); 
                conn.Close();
                return dt;
            }
            catch//(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                return new System.Data.DataTable();
            }

        }

        /// <summary>
        /// 执行SQL语句并返回DateTable
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>System.Data.DataSet</returns>
        public System.Data.DataSet execSqlReturnDataSet(System.Data.SqlClient.SqlConnection  conn ,
                                                           string sqlString)
        {
            try
            {
                if (conn.State == System.Data.ConnectionState.Closed )
                {
                    conn.Open();
                }
                System.Data.SqlClient.SqlCommand cmd     =  new System.Data.SqlClient.SqlCommand(sqlString , conn);
                cmd.CommandTimeout = 36000 ;
                System.Data.SqlClient.SqlDataAdapter dap = new System.Data.SqlClient.SqlDataAdapter(cmd);
                System.Data.DataSet ds = new System.Data.DataSet();
                dap.Fill(ds);              
                cmd.Dispose();
                conn.Close();
                dap.Dispose();              
                return ds;
            }
            catch//(Exception exp)
            {
                // System.Windows.Forms.MessageBox.Show(exp.Message);
                return new System.Data.DataSet();
            }

        }
      
        /// <summary>
        /// 执行SQL语句并返回DateTable
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <param name="sqlString">SQL语句</param>
        public Boolean execSql(System.Data.SqlClient.SqlConnection  conn , string sqlString)
        {
            try
            {
                if (conn.State == System.Data.ConnectionState.Closed )
                {
                    conn.Open();
                }
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlString , conn);
                cmd.CommandTimeout = 36000 ;
                int i = cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
                return true;
            }          
//            catch//(NullReferenceException Nexp)
//            {               
//                //System.Windows.Forms.MessageBox.Show(Nexp.Message);
//                return false;
//            }
            catch//(Exception exp)
            {
                //System.Windows.Forms.MessageBox.Show(exp.Message);
                return false;
            }

        }
       
        #endregion
    }


/// <summary>
    /// 对称加密算法类
    /// </summary>
    public class SymmetricMethod
    {
 
        private SymmetricAlgorithm mobjCryptoService;
        private string Key;
        /// <summary>
        /// 对称加密类的构造函数
        /// </summary>
        public SymmetricMethod()
        {
            mobjCryptoService = new RijndaelManaged();
            Key = @"Guz(%&hj7x89H$yuBI0456FtmaT5&fvHUFCy76*h%(HilJ$lhj!y6&(*jkP87jH7";
        }
        /// <summary>
        /// 获得密钥
        /// </summary>
        /// <returns>密钥</returns>
        private byte[] GetLegalKey()
        {
            string sTemp = Key;
            mobjCryptoService.GenerateKey();
            byte[] bytTemp = mobjCryptoService.Key;
            int KeyLength = bytTemp.Length;
            if (sTemp.Length > KeyLength)
                sTemp = sTemp.Substring(0, KeyLength);
            else if (sTemp.Length < KeyLength)
                sTemp = sTemp.PadRight(KeyLength, ' ');
            return ASCIIEncoding.ASCII.GetBytes(sTemp);
        }
        /// <summary>
        /// 获得初始向量IV
        /// </summary>
        /// <returns>初试向量IV</returns>
        private byte[] GetLegalIV()
        {
            string sTemp = @"E4ghj*Ghg7!rNIfb&95GUY86GfghUb#er57HBh(u%g6HJ($jhWk7&!hg4ui%$hjk";
            mobjCryptoService.GenerateIV();
            byte[] bytTemp = mobjCryptoService.IV;
            int IVLength = bytTemp.Length;
            if (sTemp.Length > IVLength)
                sTemp = sTemp.Substring(0, IVLength);
            else if (sTemp.Length < IVLength)
                sTemp = sTemp.PadRight(IVLength, ' ');
            return ASCIIEncoding.ASCII.GetBytes(sTemp);
        }
        /// <summary>
        /// 加密方法
        /// </summary>
        /// <param name="Source">待加密的串</param>
        /// <returns>经过加密的串</returns>
        public string Encrypto(string Source)
        {
            byte[] bytIn = UTF8Encoding.UTF8.GetBytes(Source);
            MemoryStream ms = new MemoryStream();
            mobjCryptoService.Key = GetLegalKey();
            mobjCryptoService.IV = GetLegalIV();
            ICryptoTransform encrypto = mobjCryptoService.CreateEncryptor();
            CryptoStream cs = new CryptoStream(ms, encrypto, CryptoStreamMode.Write);
            cs.Write(bytIn, 0, bytIn.Length);
            cs.FlushFinalBlock();
            ms.Close();
            byte[] bytOut = ms.ToArray();
            return Convert.ToBase64String(bytOut);
        }
        /// <summary>
        /// 解密方法
        /// </summary>
        /// <param name="Source">待解密的串</param>
        /// <returns>经过解密的串</returns>
        public string Decrypto(string Source)
        {
            try
            {
                byte[] bytIn = Convert.FromBase64String(Source);
                MemoryStream ms = new MemoryStream(bytIn, 0, bytIn.Length);
                mobjCryptoService.Key = GetLegalKey();
                mobjCryptoService.IV = GetLegalIV();
                ICryptoTransform encrypto = mobjCryptoService.CreateDecryptor();
                CryptoStream cs = new CryptoStream(ms, encrypto, CryptoStreamMode.Read);
                StreamReader sr = new StreamReader(cs);
                return sr.ReadToEnd();
            }
            catch(Exception exp)
            {
                return "";
            }
        }
    } 
XML介绍及入门使用
AtlantisChina
10-13 416
XML可扩展标记语言的认识和基础使用方法。
[code]根据xml配置文件得到数据库连接Connection
janckywong963的专栏
08-23 139
Java SAX解析器解析XML配置文件 [url]http://www.cnblogs.com/foresthjj/articles/863147.html[/url] 配置文件格式: [code="xml"] com.mysql.jdbc.Driver 127.0.0.1 test root root [/code] ...
java URL类、URLConnection类读取html或xml信息
chen879733672的专栏
08-29 766
package com.risencn.action; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; import java.net.MalformedURLException; import java.net.URL; import java
dom4j解析XML报ConnectionException解决
chiqind612229的博客
10-31 173
记录下，因为Dom4j会去有DTD声明的xml那个地址去下载验证文件，会请求网络，如果不能连接，会报错 ，通过设置如下解决 SAXReader reader = new SAXReader(); reader.setFeature("http://apache.org/xml...
AutoHotKey 使用ADODB读取Excel 报ADODB.Connection 未找到提供程序，可能未提供
weixin_33739627的博客
09-21 1011
一、系统环境 操作系统：Win7 64位 英文版 Office： Office 2010 64位/32位 AutoHotKey:AutoHotKey 1.1.26.01 二、问题现象 安装了AutoHotKey后，点击编辑好的XXX.ahk文件，报错误信息：ADODB.Connection未找到提供程序，该程序可能未正确安装 ...
两种获取connectionString的方式
weixin_33859665的博客
10-14 1452
两种获取connectionString的方式 1. public static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; &lt;connectionStrings&gt;   &lt;add name="Connection...
c#连接access数据库操作类分享
09-04
C#连接Access数据库操作类的知识点主要包括以下几个方面： 1. Web.config文件配置： 在.NET开发中，web.config文件是用来存储配置信息的XML文件。在配置Access数据库连接时，需要在web.config文件中的节点中定义...
C# winfrom操作xml_读写修改xml及写入数据库
10-27
在本文中，我们将深入探讨如何使用C# WinForm应用程序来操作XML文件，包括读取、写入、修改XML数据，并将这些数据存入数据库。XML（eXtensible Markup Language）是一种灵活的数据交换格式，常用于存储结构化数据。...
微软官方SqlHelper类 数据库辅助操作类 原创
10-22
微软官方的SqlHelper类是.NET框架中用于数据库操作的一个实用工具类，主要目的是为了简化数据库的CRUD（创建、读取、更新、删除）操作，提高开发效率，并遵循最佳实践，确保性能和可扩展性。这个类库在PetShop4.0...
ASP.net与SQLite数据库通过js和ashx交互（连接和操作）
10-20
3. 编写数据库操作辅助类：创建一个名为`SQLiteHelper.cs`的类，使用ADO.NET接口操作SQLite数据库。例如，一个简单的插入操作可能如下： ```csharp using System.Data.SQLite; public static class SQLiteHelper {...
C#通过ftp读取DT文件，将读取到的数据保存到Oracle数据库中。
03-29
在这个场景中，我们关注的是如何利用C#通过FTP（File Transfer Protocol）协议来读取DT（可能是数据传输或特定格式的文本文件）文件，并将这些数据保存到Oracle数据库中。以下是对这个过程的详细阐述： 1. FTP操作...
mysql连接字符串加密配置_如何实现数据库连接字符串加密问题
weixin_42411715的博客
01-18 641
//这我也不记得是从哪个网络见过以下代码，在实现数据库连接字符串加密很实用。记下，以备查！//SymmetricMethod.csusing System;using System.IO;using System.Security.Cryptography;using System.Text;using System.Configuration;namespace JC{/// /// 对称加密算...
连接字符串的加密和解密
李艳平
09-11 1573
.//  这个方法是在网上找到的，自己看懂了！拿来大家分享一下！    ///             /// 对称加密算法类             ///       public class SymmetricMethod       {             private SymmetricAlgorithm mobjCryptoService;
在xml文件中配置连接数据库条件连接符&转义
此人很懒，什么都没有写
06-02 2145
当我们使用xml配置文件配置连接数据库运行时可能会报一些警告： Tue Jun 02 10:40:19 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by defa
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值