C#编程连接数据库，通过更改配置文件切换数据库功能。

2023-01-05 09:29:01

该实例主要应用情景：假如某公司用mysql当做数据库服务器，由于发现mysql数据库在运行中出现不稳定情况，针对这情况，厂家要求更换连接数据库方式，改用SQL server数据库，来满足产线高效稳定的要求，下面主要看看如何实现，设计一个统一的接口IDataAccess,将SQLServerAccess,MySQLServerAccess继承这个接口，当使用数据库Sql server,实例化SQLServerAccess,相应地，如果使用MySQL,实例化MySQLServerAccess,这就是多态。

开发环境

开发工具：Microsoft Visual Studio 旗舰版、SQL Server 2008、MySQL

开发环境：.NET Framework 4 Client Profile.

实现步骤

1、采用MVC思想建立框架、建立PERSON_T表格；

首先，了解什么是MVC框架,MVC全名是Model View Controller，是模型(model)－视图(view)－控制器(controller)的缩写，一种软件设计典范，用于组织代码用一种业务逻辑和数据显示分离的方法，这个方法的假设前提是如果业务逻辑被聚集到一个部件里面，而且界面和用户围绕数据的交互能被改进和个性化定制而不需要重新编写业务逻辑MVC被独特的发展起来用于映射传统的输入、处理和输出功能在一个逻辑的图形化用户界面的结构中。

我在VS项目中建立一个目录如下图

建立SQL与Mysql数据库表名PERSON_T,具体见下代码：

   1:  USE [FMSDB]

   2:  GO

   3:

   4:  /****** Object:  Table [dbo].[PERSON_T]    Script Date: 08/05/2013 22:40:39 ******/

   5:  SET ANSI_NULLS ON

   6:  GO

   7:

   8:  SET QUOTED_IDENTIFIER ON

   9:  GO

  10:

  11:  CREATE TABLE [dbo].[PERSON_T](

  12:      [ID] [nvarchar](50) NULL,

  13:      [NAME] [nvarchar](50) NULL,

  14:      [AGE] [int] NULL,

  15:      [POSITION] [nvarchar](50) NULL,

  16:      [HOMEADDRESS] [nvarchar](50) NULL,

  17:      [IDENTIFYNUMBER] [nvarchar](50) NULL

  18:  ) ON [PRIMARY]

  19:

  20:  GO

2、主要讲讲如何实现SQL与Mysql数据库的切换。

  首先：得采用3层架构的思想来开发程序，那我们就可以从M层（即从连接数据库层修改），无需对V层(视图层)修改。

  然后：在DataGateway下建立IDataAccess.cs接口与MySQLAccess.cs、SQLServerAccess.cs、OracleDataAccess.cs类，在IDataAccess.cs接口里存放对数据库进行操作的方法。在MySQLAccess.cs、SQLServerAccess.cs、OracleDataAccess.cs中添加对数据库实际操作的方法。

IDataAccess.cs类中具体代码如下：

   1:  using System.Data;

2:

   3:  namespace ListBoxUnit1.DataGateway

   4:  {

   5:     public interface IDataAccess

   6:     {

   7:         void GetSqlConnection();

   8:         DataSet GetPersonData();

   9:         int DeleteUserInfoData(string name);

  10:         DataSet GetUserByName(string name);

  11:         DataSet GetUserByIdentifyNumber(string identifyNumber);

12:

  13:         bool UpdateUserByName(string Id, string Name, string Age, string Postion, string Address,

  14:                                      string IdentifyNumber);

15:

  16:         bool InsertUserInfo(string Id, string Name, string Age, string Postion, string Address,

  17:                                    string IdentifyNumber);

  18:     }

  19:  }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

SQLServerAccess.cs类中具体代码如下：

   1:  using System;

   2:  using System.Data;

   3:  using System.Data.SqlClient;

4:

   5:  namespace ListBoxUnit1.DataGateway

   6:  {

   7:      class SQLServerAccess : IDataAccess

   8:      {

   9:          private string connectString;

10:

  11:          private SqlConnection sqlConnection;

12:

  13:          public SQLServerAccess(string dbConnString)

  14:          {

  15:              connectString = dbConnString;

  16:              GetSqlConnection();

  17:          }

18:

  19:          #region IDataAccess 成员

20:

  21:          public void GetSqlConnection()

  22:          {

  23:              SqlConnection conn = new SqlConnection(connectString);

  24:              sqlConnection = conn;

  25:              conn.Open();

  26:          }

27:

  28:          public DataSet GetPersonData()

  29:          {

  30:              DataSet ds = new DataSet();

  31:              string sqlText = @"SELECT * FROM PERSON_T order by ID ;";

  32:              try

  33:              {

  34:                  //SqlConnection conn = GetSqlConnection();

  35:                  SqlCommand sqlCommand = sqlConnection.CreateCommand();

  36:                  sqlCommand.CommandText = sqlText;

  37:                  sqlCommand.CommandType = CommandType.Text;

  38:                  SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

  39:                  sqlDataAdapter.Fill(ds);

  40:              }

  41:              catch (Exception ex)

  42:              {

  43:                  Console.WriteLine(ex.Message);

  44:              }

  45:              return ds;

  46:          }

47:

  48:          public int DeleteUserInfoData(string name)

  49:          {

  50:              string sqlText = @"delete  FROM PERSON_T where NAME='{0}';";

  51:              sqlText = string.Format(sqlText, name);

  52:              try

  53:              {

  54:                  //SqlConnection conn = GetSqlConnection();

  55:                  SqlCommand sqlCommand = sqlConnection.CreateCommand();

  56:                  sqlCommand.CommandText = sqlText;

  57:                  sqlCommand.CommandType = CommandType.Text;

  58:                  int i = sqlCommand.ExecuteNonQuery();

  59:                  return i;

  60:              }

  61:              catch (Exception ex)

  62:              {

  63:                  return 0;

  64:              }

  65:          }

66:

  67:          public DataSet GetUserByName(string name)

  68:          {

  69:              DataSet ds = new DataSet();

  70:              string sqlText = @"SELECT * FROM PERSON_T where NAME='{0}';";

  71:              sqlText = string.Format(sqlText, name);

  72:              try

  73:              {

  74:                  SqlCommand sqlCommand = sqlConnection.CreateCommand();

  75:                  sqlCommand.CommandText = sqlText;

  76:                  sqlCommand.CommandType = CommandType.Text;

  77:                  SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

  78:                  sqlDataAdapter.Fill(ds);

  79:              }

  80:              catch (Exception ex)

  81:              {

  82:                  Console.WriteLine(ex.Message);

  83:              }

  84:              return ds;

  85:          }

86:

  87:          public DataSet GetUserByIdentifyNumber(string identifyNumber)

  88:          {

  89:              DataSet ds = new DataSet();

  90:              string sqlText = @"SELECT * FROM PERSON_T where IDENTIFYNUMBER='{0}';";

  91:              sqlText = string.Format(sqlText, identifyNumber);

  92:              try

  93:              {

  94:                  SqlCommand sqlCommand = sqlConnection.CreateCommand();

  95:                  sqlCommand.CommandText = sqlText;

  96:                  sqlCommand.CommandType = CommandType.Text;

  97:                  SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

  98:                  sqlDataAdapter.Fill(ds);

  99:              }

 100:              catch (Exception ex)

 101:              {

 102:                  Console.WriteLine(ex.Message);

 103:              }

 104:              return ds;

 105:          }

 106:

 107:          public bool UpdateUserByName(string Id, string Name, string Age, string Postion, string Address, string IdentifyNumber)

 108:          {

 109:              string sqlText =

 110:                  @"update PERSON_T set ID='{0}',NAME='{1}',AGE='{2}',POSITION='{3}',HOMEADDRESS='{4}',IDENTIFYNUMBER='{5}' FROM PERSON_T where NAME='{1}';";

 111:              sqlText = string.Format(sqlText, Id, Name, Age, Postion, Address, IdentifyNumber);

 112:              try

 113:              {

 114:                  SqlCommand sqlCommand = sqlConnection.CreateCommand();

 115:                  sqlCommand.CommandText = sqlText;

 116:                  sqlCommand.CommandType = CommandType.Text;

 117:                  int i = sqlCommand.ExecuteNonQuery();

 118:                  return true;

 119:

 120:              }

 121:              catch (Exception ex)

 122:              {

 123:                  return false;

 124:              }

 125:          }

 126:

 127:          public bool InsertUserInfo(string Id, string Name, string Age, string Postion, string Address, string IdentifyNumber)

 128:          {

 129:              string sqlText =

 130:                      @"Insert into PERSON_T (ID,NAME,AGE,POSITION,HOMEADDRESS,IDENTIFYNUMBER)Values('{0}','{1}','{2}','{3}','{4}','{5}');";

 131:              sqlText = string.Format(sqlText, Id, Name, Age, Postion, Address, IdentifyNumber);

 132:              try

 133:              {

 134:                  SqlCommand sqlCommand = sqlConnection.CreateCommand();

 135:                  sqlCommand.CommandText = sqlText;

 136:                  sqlCommand.CommandType = CommandType.Text;

 137:                  int i = sqlCommand.ExecuteNonQuery();

 138:                  return true;

 139:              }

 140:              catch (Exception ex)

 141:              {

 142:                  return false;

 143:              }

 144:          }

 145:

 146:          #endregion

 147:      }

 148:  }

MySQLAccess.cs类中具体代码如下：

   1:  using System;
   2:  using System.Data;
   3:  using MySql.Data.MySqlClient;
   4:   
   5:  namespace ListBoxUnit1.DataGateway
   6:  {
   7:      class MySQLAccess : IDataAccess
   8:      {
   9:          //定义连接Mysql的对象的属性
  10:          private string connectString;
  11:   
  12:          private MySqlConnection mySqlConnection;
  13:   
  14:          #region IDataAccess 成员
  15:   
  16:          public MySQLAccess(string dbConnString)
  17:          {
  18:              connectString = dbConnString;
  19:              GetSqlConnection();
  20:          }
  21:   
  22:          public void GetSqlConnection()
  23:          {
  24:              MySqlConnection conn = new MySqlConnection(connectString);
  25:              mySqlConnection = conn;
  26:              conn.Open();
  27:          }
  28:   
  29:          public DataSet GetPersonData()
  30:          {
  31:              DataSet ds = new DataSet();
  32:              string mySqlText = @"SELECT * FROM PERSON_T order by ID ;";
  33:              try
  34:              {
  35:                  MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
  36:                  mySqlCommand.CommandText = mySqlText;
  37:                  mySqlCommand.CommandType = CommandType.Text;
  38:                  MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySqlCommand);
  39:                  mySqlDataAdapter.Fill(ds);
  40:              }
  41:              catch (Exception ex)
  42:              {
  43:                  Console.WriteLine(ex.Message);
  44:              }
  45:              return ds;
  46:          }
  47:   
  48:          public int DeleteUserInfoData(string name)
  49:          {
  50:              string mySqlText = @"delete  FROM PERSON_T where NAME='{0}';";
  51:              mySqlText = string.Format(mySqlText, name);
  52:              try
  53:              {
  54:                  MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
  55:                  mySqlCommand.CommandText = mySqlText;
  56:                  mySqlCommand.CommandType = CommandType.Text;
  57:                  int i = mySqlCommand.ExecuteNonQuery();
  58:                  return i;
  59:              }
  60:              catch (Exception ex)
  61:              {
  62:                  return 0;
  63:              }
  64:          }
  65:   
  66:          public DataSet GetUserByName(string name)
  67:          {
  68:              DataSet ds = new DataSet();
  69:              string mySqlText = @"SELECT * FROM PERSON_T where NAME='{0}';";
  70:              mySqlText = string.Format(mySqlText, name);
  71:              try
  72:              {
  73:                  MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
  74:                  mySqlCommand.CommandText = mySqlText;
  75:                  mySqlCommand.CommandType = CommandType.Text;
  76:                  MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySqlCommand);
  77:                  mySqlDataAdapter.Fill(ds);
  78:              }
  79:              catch (Exception ex)
  80:              {
  81:                  Console.WriteLine(ex.Message);
  82:              }
  83:              return ds;
  84:          }
  85:   
  86:          public DataSet GetUserByIdentifyNumber(string identifyNumber)
  87:          {
  88:              DataSet ds = new DataSet();
  89:              string mySqlText = @"SELECT * FROM PERSON_T where IDENTIFYNUMBER='{0}';";
  90:              mySqlText = string.Format(mySqlText, identifyNumber);
  91:              try
  92:              {
  93:   
  94:                  MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
  95:                  mySqlCommand.CommandText = mySqlText;
  96:                  mySqlCommand.CommandType = CommandType.Text;
  97:                  MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySqlCommand);
  98:                  mySqlDataAdapter.Fill(ds);
  99:              }
 100:              catch (Exception ex)
 101:              {
 102:                  Console.WriteLine(ex.Message);
 103:              }
 104:              return ds;
 105:          }
 106:   
 107:          public bool UpdateUserByName(string Id, string Name, string Age, string Postion, string Address, string IdentifyNumber)
 108:          {
 109:              string mySqlText =
 110:                 @"update PERSON_T set ID='{0}',NAME='{1}',AGE='{2}',POSITION='{3}',HOMEADDRESS='{4}',IDENTIFYNUMBER='{5}'where NAME='{1}';";
 111:              mySqlText = string.Format(mySqlText, Id, Name, Age, Postion, Address, IdentifyNumber);
 112:              try
 113:              {
 114:   
 115:                  MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
 116:                  mySqlCommand.CommandText = mySqlText;
 117:                  mySqlCommand.CommandType = CommandType.Text;
 118:                  int i = mySqlCommand.ExecuteNonQuery();
 119:                  return true;
 120:   
 121:              }
 122:              catch (Exception ex)
 123:              {
 124:                  return false;
 125:              }
 126:          }
 127:   
 128:          public bool InsertUserInfo(string Id, string Name, string Age, string Postion, string Address, string IdentifyNumber)
 129:          {
 130:              string mySqlText =
 131:                      @"Insert into PERSON_T (ID,NAME,AGE,POSITION,HOMEADDRESS,IDENTIFYNUMBER)Values('{0}','{1}','{2}','{3}','{4}','{5}');";
 132:              mySqlText = string.Format(mySqlText, Id, Name, Age, Postion, Address, IdentifyNumber);
 133:              try
 134:              {
 135:                  MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
 136:                  mySqlCommand.CommandText = mySqlText;
 137:                  mySqlCommand.CommandType = CommandType.Text;
 138:                  int i = mySqlCommand.ExecuteNonQuery();
 139:                  return true;
 140:              }
 141:              catch (Exception ex)
 142:              {
 143:                  return false;
 144:              }
 145:          }
 146:   
 147:          #endregion
 148:      }
 149:  }

.csharpcode, .csharpcode pre

{

	font-size: small;

	color: black;

	font-family: consolas, "Courier New", courier, monospace;

	background-color: #ffffff;

	/*white-space: pre;*/

}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt

{

	background-color: #f4f4f4;

	width: 100%;

	margin: 0em;

}

.csharpcode .lnum { color: #606060; }

3、App.config中具体代码如下，从中屏蔽不需要的数据库代码即可：

   1:  <?xml version="1.0" encoding="utf-8"?>

   2:  <configuration>

   3:    <appSettings>

   4:      <add key="MySQLAccess.Conn" value="server=localhost;user id=root;password=12345;database=fmsdb;character set=utf8"/>

   5:      <!--<add key="SQLServerAccess.Conn" value="Data Source=localhost,1433;Network Library=DBMSSOCN;Initial Catalog=FMSDB;User ID=root;Password=12345;" />-->

   6:      <!--<add key="OracleServerAccess.Conn" value="User Id=root;Password=12345;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=AUTHDB)(SERVER=DEDICATED)));" />-->

   7:    </appSettings>

   8:  </configuration>

技术点

1、主要熟练运用连接SQL与Mysql连接数据库的基本知识

2、创建接口，以及多个类继承该接口，使用多态

3、使用正则表达式和if…else if…语句，控制用户输入内容

4、面向对象编程概念进一步加深理解

疑惑

对于SQL与Mysql数据库建立连接的基本概念比较模糊，对于定义接口，多个类继承该接口的技术比较生疏。

感受

通过该实例的练习，已经对通过改配置文件，切换不同数据库有了一定的理解，另外对于程序的控制方面如if…else if…、do..while…控制语句有了一定的理解，另外对于正则表式的使用也有了比较清晰的概念，缺点在于对SQL连接以及Mysql连接概念不是很清楚，对于数据或参数的传递不熟练，不知道怎样把面向对象的概念深刻理解，望高人指点！！

源码下载

码农公寓

开发环境

实现步骤

技术点

疑惑

感受

相关文章