C# 数据库操作封装

时间：2022-09-20 18:22:44

绝大多数程序，想要实现数据交互，数据存储，都离不开数据库，无论是桌面客户端应用程序，网站，还是手机APP。这里讲解了C#中MySQL数据库操作封装，如其它数据库（SQLServer也差不多），封装了sql语句的查询、修改、插入、删除操作，以及存储过程的执行，包括有输入、输出参数的存储过程，存储过程的执行无需输入任何参数名称，只需输入参数值即可。

1.数据库封装接口

using System;
using System.Data;

namespace DBClass
{
public interface IDBfun : IDisposable
    {
string ConnectionString { get; set; }

void SetDBConnectionPararmeter(string server,
string database,
string username,
string password,
int port = 3306);

int ExecSQL(string sqlString, object[] prams);
int ExecSQL(string sqlString);

        DataSet ExecSQLQuery(string sqlString, //命令文本
object[] prams); //参数对象

        DataSet ExecSQLQuery(string sqlString, //命令文本
object[] prams, //参数对象
string tbName); //数据表名称

        DataSet ExecSQLQuery(string sqlString, //命令文本
object[] prams, //参数对象
string tbName, //数据表名称
ref object objcmd //返回SqlCommand
            );

        DataSet ExecSQLQuery(object objcmd); //执行SqlCommand
        DataSet ExecSQLQuery(string sqlString);

object MakeInParam(string paramName, //存储过程名称或命令文本
            SqlDbType dbType, //参数类型
int size, //参数大小
object value); //参数值

int SavePictureToDB(byte[] imageByte,
string picturename,
string picType,
int picTypeId);

int UpdatePictureToDb(byte[] imageByte, string pictureName, string picType, int picTypeId);

int GetPictureFromDB(ref byte[] byPictureBytes,
string picType,
int picTypeId);
    }
}

2.继承接口实现

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace DBClass
{
public class DBMySQL : IDBfun
    {
public string ConnectionString { get; set; }

public DBMySQL()
        {
            ConnectionString = "";
        }

public DBMySQL(string server,
string database,
string username,
string password,
int port = 3306)
        {
            ConnectionString = "Data Source=" + server + //创建数据库连接串
";DataBase=" + database +
";User ID=" + username +
";Password=" + password +
";Port=" + port +
";Charset=utf8" +
";default command timeout=0" + //此字符表示将命令超时时间改为无限等待
";Allow Zero DateTime=True"; //此字符串用于将数据库的DateTime类型装换
        }

public void Dispose()
        {
        }

public void SetDBConnectionPararmeter(string server,
string database,
string username,
string password,
int port = 3306)
        {
            ConnectionString = "Data Source=" + server + //创建数据库连接串
";DataBase=" + database +
";User ID=" + username +
";Password=" + password +
";Port=" + port +
";Charset=utf8";
        }

/// <summary>
/// 保存图片到数据库
/// </summary>
/// <param name="imageByte"></param>
/// <param name="picturename"></param>
/// <param name="picType"></param>
/// <param name="picTypeId"></param>
/// <returns></returns>
public int SavePictureToDB(byte[] imageByte,
string picturename,
string picType,
int picTypeId)
        {
var result = 0;
try
            {
if (imageByte != null && imageByte.Length != 0)
                {
using (var conn = new MySqlConnection())
                    {
                        conn.ConnectionString = ConnectionString;
                        conn.Open();

var insertStr = @"INSERT INTO studentmanager.picture 
 (
 Picturename, 
 PicType, 
 PicTypeId, 
 imageByte
 )
 VALUES
 (
 @Picturename, 
 @PicType, 
 @PicTypeId, 
 @imageByte
 );";
var comm = new MySqlCommand();
                        comm.Connection = conn;
                        comm.CommandText = insertStr;
                        comm.CommandType = CommandType.Text;

//设置数据库字段类型MediumBlob的值为图片字节数组imageByte
                        comm.Parameters.Add(new MySqlParameter("@imageByte", MySqlDbType.MediumBlob)).Value = imageByte;
                        comm.Parameters.Add(new MySqlParameter("@Picturename", MySqlDbType.VarChar)).Value = picturename;
                        comm.Parameters.Add(new MySqlParameter("@PicType", MySqlDbType.VarChar)).Value = picType;
                        comm.Parameters.Add(new MySqlParameter("@PicTypeId", MySqlDbType.Int32)).Value = picTypeId;
//execute sql
                        result = comm.ExecuteNonQuery();

                        comm.Dispose();
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
catch (Exception)
            {
// throw ex;
            }
return result;
        }

/// <summary>
/// 更新图片
/// </summary>
/// <param name="imageByte"></param>
/// <param name="pictureName"></param>
/// <param name="picType"></param>
/// <param name="picTypeId"></param>
/// <returns></returns>
public int UpdatePictureToDb(byte[] imageByte, string pictureName, string picType, int picTypeId)
        {
var result = 0;
try
            {
if (imageByte != null && imageByte.Length != 0)
                {
using (var conn = new MySqlConnection())
                    {
                        conn.ConnectionString = ConnectionString;
                        conn.Open();

var insertStr = @"UPDATE studentmanager.picture 
 SET
 Picturename = @Picturename , 
 imageByte = @imageByte

 WHERE
 PicType = @PicType AND PicTypeId = @PicTypeId ";
var comm = new MySqlCommand
                        {
                            Connection = conn,
                            CommandText = insertStr,
                            CommandType = CommandType.Text
                        };

//设置数据库字段类型MediumBlob的值为图片字节数组imageByte
                        comm.Parameters.Add(new MySqlParameter("@imageByte", MySqlDbType.MediumBlob)).Value = imageByte;
                        comm.Parameters.Add(new MySqlParameter("@Picturename", MySqlDbType.VarChar)).Value = pictureName;
                        comm.Parameters.Add(new MySqlParameter("@PicType", MySqlDbType.VarChar)).Value = picType;
                        comm.Parameters.Add(new MySqlParameter("@PicTypeId", MySqlDbType.Int32)).Value = picTypeId;
//execute sql
                        result = comm.ExecuteNonQuery();

                        comm.Dispose();
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
catch (Exception)
            {
// throw ex;
            }
return result;
        }

public int GetPictureFromDB(ref byte[] byPictureBytes,
string picType,
int picTypeId)
        {
try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

//设置命令参数
var sqLstring = $@"SELECT 
 imageByte

 FROM 
 studentmanager.picture 
 WHERE PicType ='{picType}' AND PicTypeId='{picTypeId}'";
var comm = new MySqlCommand(sqLstring, conn);

//执行命令并获得数据读取器
var dr = comm.ExecuteReader();
if (dr.Read())
                    {
try
                        {
//读出图片字节数组至byte[]
var len = dr.GetBytes(0, 0, null, 0, int.MaxValue);
                            byPictureBytes = new byte[len];
if (byPictureBytes.Length != 0)
                                dr.GetBytes(0, 0, byPictureBytes, 0, byPictureBytes.Length);
                        }
catch (Exception)
                        {
// ignored
                        }
                    }
                    dr.Dispose();
                    comm.Dispose();
                    conn.Close();
                    conn.Dispose();
                }
            }
catch (Exception)
            {
// throw ex;
            }
return 0;
        }

public DataSet ExecSQLQuery(string sqlString, object[] prams)
        {
            MySqlDataAdapter dap;
            DataSet dsRet = null;

if (string.IsNullOrEmpty(ConnectionString))
            {
throw new Exception("ConnectionString Error");
            }

if (string.IsNullOrEmpty(sqlString))
            {
throw new Exception("SQL String Error");
            }

if (!DsafConnectionTest())
            {
throw new Exception("Database Connect Error");
            }

try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

                    dap = CreateDataAdaper(sqlString, prams, conn); //创建桥接器对象
                    dsRet = new DataSet(); //创建数据集对象
                    dap.Fill(dsRet); //填充数据集

                    conn.Close();
                    conn.Dispose();
                }
            }
catch (MySqlException)
            {
// // throw ex;
            }
catch (Exception)
            {
// // throw ex;
            }
return dsRet; //返回数据集
        }

/// <summary>
/// 初始化参数值
/// </summary>
private object MakeParam(string paramName, //存储过程名称或命令文本
            MySqlDbType dbType, //参数类型
int size, //参数大小
            ParameterDirection direction, //参数方向
object value) //参数值
        {
            MySqlParameter param; //声明SQL参数对象
if (size > 0) //判断参数字段是否大于0
                param = new MySqlParameter(paramName, dbType, size); //根据指定的类型和大小创建SQL参数
else
                param = new MySqlParameter(paramName, dbType); //根据指定的类型创建SQL参数

            param.Direction = direction; //设置SQL参数的类型
if (!(direction == ParameterDirection.Output && value == null)) //判断是否为输出参数
                param.Value = value; //设置参数返回值

//转换object对象返回
return param; //返回SQL参数
        }

#region 将命令文本添加到SqlDataAdapter

/// <summary>
/// 创建一个SqlDataAdapter对象以此来执行命令文本
/// </summary>
private MySqlDataAdapter CreateDataAdaper(string sqLstring, //命令文本
object[] prams,
            MySqlConnection conn) //参数对象
        {
var dap = new MySqlDataAdapter(sqLstring, conn); //创建桥接器对象
            dap.SelectCommand.CommandType = CommandType.Text; //指定要执行的类型为命令文本

if (prams != null) //判断SQL参数是否不为空
            {
foreach (var parameter in prams) //遍历传递的每个SQL参数
                {
var param = (MySqlParameter)parameter;
                    dap.SelectCommand.Parameters.Add(param); //将SQL参数添加到执行命令对象中
                }
            }

//加入返回参数
            dap.SelectCommand.Parameters.Add(new MySqlParameter("ReturnValue",
                MySqlDbType.Int32,
4,
                ParameterDirection.ReturnValue,
false,
0,
0,
string.Empty,
                DataRowVersion.Default,
null));


//返回桥接器对象
return dap;
        }

#endregion

#region 将命令文本添加到SqlCommand

/// <summary>
/// 创建一个SqlCommand对象以此来执行命令文本
/// </summary>
private MySqlCommand CreateCommand(string procName, //命令文本
object[] prams,
            MySqlConnection conn) //命令文本所需参数
        {
var cmd = new MySqlCommand(procName, conn); //创建SqlCommand命令对象
            cmd.CommandType = CommandType.Text; //指定要执行的类型为命令文本

// 依次把参数传入命令文本
if (prams != null) //判断SQL参数是否不为空
            {
foreach (var parameter in prams) //遍历传递的每个SQL参数
                {
var param = (MySqlParameter)parameter;
                    cmd.Parameters.Add(param); //将SQL参数添加到执行命令对象中
                }
            }

//加入返回参数
            cmd.Parameters.Add(new MySqlParameter("ReturnValue",
                MySqlDbType.Int32,
4,
                ParameterDirection.ReturnValue,
false,
0,
0,
string.Empty,
                DataRowVersion.Default,
null));

//返回SqlCommand命令对象
return cmd;
        }

#endregion

#region 执行参数命令文本(无数据库中数据返回)

/// <summary>
/// 执行命令
/// </summary>
public int ExecSQL(string sqlString, //命令文本
object[] prams) //参数对象
        {
var nRet = 0;
            MySqlCommand cmd;
            MySqlTransaction transaction = null;

if (string.IsNullOrEmpty(ConnectionString))
            {
throw new Exception("ConnectionString Error");
            }

if (string.IsNullOrEmpty(sqlString))
            {
throw new Exception("SQL String Error");
            }

if (!DsafConnectionTest())
            {
throw new Exception("Database Connect Error");
            }

try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

                    transaction = conn.BeginTransaction();
                    cmd = CreateCommand(sqlString, prams, conn); //创建SqlCommand命令对象
                    cmd.Transaction = transaction;

                    nRet = cmd.ExecuteNonQuery(); //执行SQL命令
                    transaction.Commit();

                    conn.Close();
                    conn.Dispose();
                }
            }
catch (MySqlException)
            {
                transaction?.Rollback();
            }
catch (Exception)
            {
// ignored
            }
return nRet;
        }

/// <summary>
/// 直接执行SQL语句
/// </summary>
public int ExecSQL(string sqlString)
        {
var nRet = 0;

            MySqlCommand cmd;
            MySqlTransaction transaction = null;

if (string.IsNullOrEmpty(ConnectionString))
            {
throw new Exception("ConnectionString Error");
            }

if (string.IsNullOrEmpty(sqlString))
            {
throw new Exception("SQL String Error");
            }

if (!DsafConnectionTest())
            {
throw new Exception("Database Connect Error");
            }

try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

                    transaction = conn.BeginTransaction();
                    cmd = new MySqlCommand(sqlString, conn); //创建SqlCommand命令对象
                    cmd.Transaction = transaction;
                    nRet = cmd.ExecuteNonQuery(); //执行SQL命令
                    transaction.Commit();

                    conn.Close();
                    conn.Dispose();
                }
            }
catch (MySqlException)
            {
                transaction?.Rollback();
            }
catch (Exception)
            {
// ignored
            }
//返回>0，表示执行成功
return nRet;
        }

#region 执行参数命令文本(有返回值)

/// <summary>
/// 执行查询命令文本，并且返回DataSet数据集
/// </summary>
public DataSet ExecSQLQuery(string sqlString, //命令文本
object[] prams, //参数对象
string tbName) //数据表名称
        {
            MySqlDataAdapter dap;
            DataSet dsRet = null;

if (string.IsNullOrEmpty(ConnectionString))
            {
throw new Exception("ConnectionString Error");
            }

if (string.IsNullOrEmpty(sqlString))
            {
throw new Exception("SQL String Error");
            }

if (!DsafConnectionTest())
            {
throw new Exception("Database Connect Error");
            }

try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

                    dap = CreateDataAdaper(sqlString, prams, conn); //创建桥接器对象
                    dsRet = new DataSet(); //创建数据集对象
                    dap.Fill(dsRet, tbName); //填充数据集

                    conn.Close();
                    conn.Dispose();
                }
            }
catch (MySqlException)
            {
// // throw ex;
            }
catch (Exception)
            {
// // throw ex;
            }
return dsRet; //返回数据集
        }

public DataSet ExecSQLQuery(string sqlString, //命令文本
object[] prams, //参数对象
string tbName, //数据表名称
ref object cmd //返回SQL command
            )
        {
            MySqlDataAdapter dap;
            DataSet dsRet = null;

if (string.IsNullOrEmpty(ConnectionString))
            {
throw new Exception("ConnectionString Error");
            }

if (string.IsNullOrEmpty(sqlString))
            {
throw new Exception("SQL String Error");
            }

if (!DsafConnectionTest())
            {
throw new Exception("Database Connect Error");
            }

try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

                    dap = CreateDataAdaper(sqlString, prams, conn); //创建桥接器对象
                    dsRet = new DataSet(); //创建数据集对象
                    dap.Fill(dsRet, tbName); //填充数据集

                    cmd = dap.SelectCommand;

                    conn.Close();
                    conn.Dispose();
                }
            }
catch (MySqlException)
            {
// throw ex;
            }
catch (Exception)
            {
// throw ex;
            }
return dsRet; //返回数据集
        }

//执行一个SQL命令体
public DataSet ExecSQLQuery(object cmd) //命令对象
        {
            MySqlDataAdapter dap;
            DataSet dsRet = null;

if (string.IsNullOrEmpty(ConnectionString))
            {
throw new Exception("ConnectionString Error");
            }

if (!DsafConnectionTest())
            {
throw new Exception("Database Connect Error");
            }

try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

                    dap = new MySqlDataAdapter((MySqlCommand)cmd); //创建桥接器对象
                    dsRet = new DataSet(); //创建数据集对象
                    dap.Fill(dsRet); //填充数据集

                    conn.Close();
                    conn.Dispose();
                }
            }
catch (MySqlException)
            {
// throw ex;
            }
catch (Exception)
            {
// throw ex;
            }
return dsRet; //返回数据集
        }

public DataSet ExecSQLQuery(string sqlString)
        {
            MySqlDataAdapter dap;
            DataSet dsRet = null;

if (string.IsNullOrEmpty(ConnectionString))
            {
throw new Exception("ConnectionString Error");
            }

if (string.IsNullOrEmpty(sqlString))
            {
throw new Exception("SQL String Error");
            }

if (!DsafConnectionTest())
            {
throw new Exception("Database Connect Error");
            }

try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

                    dap = CreateDataAdaper(sqlString, null, conn); //创建桥接器对象
                    dsRet = new DataSet(); //创建数据集对象
                    dap.Fill(dsRet); //填充数据集

                    conn.Close();
                    conn.Dispose();
                }
            }
catch (MySqlException)
            {
// throw ex;
            }
catch (Exception)
            {
// throw ex;
            }
return dsRet; //返回数据集
        }

#endregion

private bool DsafConnectionTest()
        {
var bRet = false;

try
            {
using (var conn = new MySqlConnection())
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();

                    bRet = true;

                    conn.Close();
                    conn.Dispose();
                }
            }
catch (MySqlException)
            {
//// throw ex; 
// CommonFunction.MessageShow("数据库连接失败", "提示", "OK", "Information");
            }
return bRet;
        }

#endregion

#region 传入参数并且转换为SqlParameter类型

/// <summary>
/// 转换参数
/// </summary>
public object MakeInParam(string paramName, //存储过程名称或命令文本
            SqlDbType dbType, //参数类型
int size, //参数大小
object value) //参数值
        {
var mydbtype = TransTypeFromSqlDBtype(dbType);
return MakeParam(paramName, mydbtype, size, ParameterDirection.Input, value); //创建SQL参数
        }

private MySqlDbType TransTypeFromSqlDBtype(SqlDbType dbType)
        {
            MySqlDbType dbRet = 0;

switch (dbType)
            {
case SqlDbType.BigInt:
                    dbRet = MySqlDbType.Int32;
break;
case SqlDbType.Binary:
                    dbRet = MySqlDbType.Binary;
break;
case SqlDbType.Char:
                    dbRet = MySqlDbType.Byte;
break;
case SqlDbType.Date:
                    dbRet = MySqlDbType.Date;
break;
case SqlDbType.DateTime:
                    dbRet = MySqlDbType.DateTime;
break;
case SqlDbType.DateTime2:
                    dbRet = MySqlDbType.DateTime;
break;
case SqlDbType.DateTimeOffset:
                    dbRet = MySqlDbType.Timestamp;
break;
case SqlDbType.Decimal:
                    dbRet = MySqlDbType.Decimal;
break;
case SqlDbType.Float:
                    dbRet = MySqlDbType.Float;
break;
case SqlDbType.Image:
                    dbRet = MySqlDbType.LongBlob;
break;
case SqlDbType.Int:
                    dbRet = MySqlDbType.Int32;
break;
case SqlDbType.Money:
                    dbRet = MySqlDbType.Double;
break;
case SqlDbType.NChar:
                    dbRet = MySqlDbType.UByte;
break;
case SqlDbType.NText:
                    dbRet = MySqlDbType.Text;
break;
case SqlDbType.NVarChar:
                    dbRet = MySqlDbType.Text;
break;
case SqlDbType.Real:
                    dbRet = MySqlDbType.Double;
break;
case SqlDbType.SmallDateTime:
                    dbRet = MySqlDbType.Newdate;
break;
case SqlDbType.SmallInt:
                    dbRet = MySqlDbType.Int16;
break;
case SqlDbType.SmallMoney:
                    dbRet = MySqlDbType.Float;
break;
case SqlDbType.Text:
                    dbRet = MySqlDbType.Text;
break;
case SqlDbType.Time:
                    dbRet = MySqlDbType.Time;
break;
case SqlDbType.Timestamp:
                    dbRet = MySqlDbType.Timestamp;
break;
case SqlDbType.TinyInt:
                    dbRet = MySqlDbType.Int16;
break;
case SqlDbType.UniqueIdentifier:
                    dbRet = MySqlDbType.Guid;
break;
case SqlDbType.VarBinary:
                    dbRet = MySqlDbType.VarBinary;
break;
case SqlDbType.VarChar:
                    dbRet = MySqlDbType.VarChar;
break;
case SqlDbType.Variant:
                    dbRet = MySqlDbType.VarString;
break;
case SqlDbType.Xml:
                    dbRet = MySqlDbType.LongText;
break;
            }
return dbRet;
        }

#endregion
    }
}

3.工程类创建数据库访问对象

namespace DBClass
{
public class DbFactory
    {
public static IDBfun Create(string dBtype)
        {
if (dBtype == "MySQL")
return new DBMySQL();
return new DBSQL();
        }

public static IDBfun Create(string dBtype,
string server,
string database,
string username,
string password,
int port = 3306)
        {
if (dBtype == "MySQL")
return new DBMySQL(server, database, username, password, port);
return new DBSQL(server, database, username, password);
        }
    }
}

4.创建实例操作数据库

using System;
using System.Data;
using Common;
using DBClass;
using MySql.Data.MySqlClient;

namespace StudentManageTool.Plugs
{
public class DBHelper
    {
private readonly IDBfun db;

public DBHelper(string DBType,
string DBServer,
string DBDataBase,
string DBUser,
string DBPwd,
int port
            )
        {
            db = DbFactory.Create(DBType, DBServer, DBDataBase, DBUser, DBPwd, port);
        }

public object MakeInParam(string ParamName, //存储过程名称或命令文本
            SqlDbType DbType, //参数类型
int Size, //参数大小
object Value) //参数值
        {
var mydbtype = TransTypeFromSqlDBtype(DbType);
return MakeParam(ParamName, mydbtype, Size, ParameterDirection.Input, Value); //创建SQL参数
        }

/// <summary>
/// 初始化参数值
/// </summary>
private object MakeParam(string ParamName, //存储过程名称或命令文本
            MySqlDbType DbType, //参数类型
int Size, //参数大小
            ParameterDirection Direction, //参数方向
object Value) //参数值
        {
            MySqlParameter param; //声明SQL参数对象
if (Size > 0) //判断参数字段是否大于0
                param = new MySqlParameter(ParamName, DbType, Size); //根据指定的类型和大小创建SQL参数
else
                param = new MySqlParameter(ParamName, DbType); //根据指定的类型创建SQL参数

            param.Direction = Direction; //设置SQL参数的类型
if (!(Direction == ParameterDirection.Output && Value == null)) //判断是否为输出参数
                param.Value = Value; //设置参数返回值

//转换object对象返回
return param; //返回SQL参数
        }

public int ExecSQLInt(string SQLString, object[] prams)
        {
return db.ExecSQL(SQLString, prams);
        }

public DataSet ExecSQL(string SQLString, object[] prams)
        {
return db.ExecSQLQuery(SQLString, prams);
        }

public DataSet GetResult(string sqlStr)
        {
var ds = new DataSet();
try
            {
                ds = db.ExecSQLQuery(sqlStr);
if (ds != null && ds.Tables[0].Rows.Count != 0)
                {
return ds;
                }
            }
catch (Exception ex)
            {
                CommonFunction.MessageShow(ex.Message);
            }
return null;
        }

public int GetResultInt(string sqlStr)
        {
try
            {
var ds = db.ExecSQL(sqlStr);
return ds;
            }
catch (Exception ex)
            {
                CommonFunction.MessageShow(ex.Message);
            }
return 0;
        }

/// <summary>
/// 保存图片
/// </summary>
/// <returns></returns>
public int SavePictureToDB(byte[] imageByte, string Picturename, string PicType, int PicTypeId)
        {
return db.SavePictureToDB(imageByte, Picturename, PicType, PicTypeId);
        }

public int UpdatePictureToDb(byte[] imageByte, string pictureName, string picType, int picTypeId)
        {
return db.UpdatePictureToDb(imageByte, pictureName, picType, picTypeId);
        }

/// <summary>
/// 获取图片
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public byte[] GetImage(string PicType, int PicTypeId)
        {
try
            {
var imageByte = new byte[0];
var result = db.GetPictureFromDB(ref imageByte, PicType, PicTypeId);
if (result == 0 && imageByte != null && imageByte.Length != 0)
                {
return imageByte;
                }
            }
catch (Exception)
            {
//CommonFunction.MessageShow(ex.Message);
            }
return null;
        }

private MySqlDbType TransTypeFromSqlDBtype(SqlDbType DbType)
        {
            MySqlDbType dbRet = 0;

switch (DbType)
            {
case SqlDbType.BigInt:
                    dbRet = MySqlDbType.Int32;
break;
case SqlDbType.Binary:
                    dbRet = MySqlDbType.Binary;
break;
case SqlDbType.Char:
                    dbRet = MySqlDbType.Byte;
break;
case SqlDbType.Date:
                    dbRet = MySqlDbType.Date;
break;
case SqlDbType.DateTime:
                    dbRet = MySqlDbType.DateTime;
break;
case SqlDbType.DateTime2:
                    dbRet = MySqlDbType.DateTime;
break;
case SqlDbType.DateTimeOffset:
                    dbRet = MySqlDbType.Timestamp;
break;
case SqlDbType.Decimal:
                    dbRet = MySqlDbType.Decimal;
break;
case SqlDbType.Float:
                    dbRet = MySqlDbType.Float;
break;
case SqlDbType.Image:
                    dbRet = MySqlDbType.LongBlob;
break;
case SqlDbType.Int:
                    dbRet = MySqlDbType.Int32;
break;
case SqlDbType.Money:
                    dbRet = MySqlDbType.Double;
break;
case SqlDbType.NChar:
                    dbRet = MySqlDbType.UByte;
break;
case SqlDbType.NText:
                    dbRet = MySqlDbType.Text;
break;
case SqlDbType.NVarChar:
                    dbRet = MySqlDbType.Text;
break;
case SqlDbType.Real:
                    dbRet = MySqlDbType.Double;
break;
case SqlDbType.SmallDateTime:
                    dbRet = MySqlDbType.Newdate;
break;
case SqlDbType.SmallInt:
                    dbRet = MySqlDbType.Int16;
break;
case SqlDbType.SmallMoney:
                    dbRet = MySqlDbType.Float;
break;
case SqlDbType.Text:
                    dbRet = MySqlDbType.Text;
break;
case SqlDbType.Time:
                    dbRet = MySqlDbType.Time;
break;
case SqlDbType.Timestamp:
                    dbRet = MySqlDbType.Timestamp;
break;
case SqlDbType.TinyInt:
                    dbRet = MySqlDbType.Int16;
break;
case SqlDbType.UniqueIdentifier:
                    dbRet = MySqlDbType.Guid;
break;
case SqlDbType.VarBinary:
                    dbRet = MySqlDbType.VarBinary;
break;
case SqlDbType.VarChar:
                    dbRet = MySqlDbType.VarChar;
break;
case SqlDbType.Variant:
                    dbRet = MySqlDbType.VarString;
break;
case SqlDbType.Xml:
                    dbRet = MySqlDbType.LongText;
break;
            }
return dbRet;
        }
    }
}
标签：

相关文章