我的[SQLHelper]C#版

[ssy888]

using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.OracleClient;


[Serializable]
public class SQLHelper
{
public enum dataType
{
VarChar,
NVarChar,
DateTime,
Int,
SmallInt,
VarBinary,
Bool,
Money,
BigInt,
Text,
CharFixed,
Dec,
}

private enum ConnectionType
{
SQL,
OleDb,
ODBC,
Oracle,
}

public static IDbCommand MakeCommand(string command, IDbConnection con, CommandType commandType)
{
try
{
if(con.GetType() == typeof(SqlConnection))
{
SqlCommand cmd;
cmd = new SqlCommand(command, (SqlConnection)con);
cmd.CommandType = commandType;
return cmd;
}
else if(con.GetType() == typeof(OleDbConnection))
{
OleDbCommand cmd;
cmd = new OleDbCommand(command, (OleDbConnection)con);
cmd.CommandType = commandType;
return cmd;
}
else if(con.GetType() == typeof(OdbcConnection))
{
OdbcCommand cmd;
cmd = new OdbcCommand(command, (OdbcConnection)con);
cmd.CommandType = commandType;
return cmd;
}
else if (con.GetType() == typeof(OracleConnection))
{
OracleCommand cmd;
cmd = new OracleCommand(command, (OracleConnection)con);
cmd.CommandType = commandType;
return cmd;
}
else
return null;
}
catch(Exception exc)
{
throw new Exception("Error in MakeCommand", exc);
}
}

public static IDataParameter MakeParameter(IDbCommand command, string name, dataType type, ParameterDirection direction)
{
try
{
if(command.GetType() == typeof(SqlCommand))
{
SqlParameter parameter;
parameter = new SqlParameter();
parameter.ParameterName = name;
parameter.Direction = direction;
parameter.SqlDbType = (SqlDbType)ConvertDbType(ConnectionType.SQL, type);
command.Parameters.Add(parameter);
return parameter;
}
else if(command.GetType() == typeof(OleDbCommand))
{
OleDbParameter parameter;
parameter = new OleDbParameter();
parameter.ParameterName = name;
parameter.Direction = direction;
parameter.DbType = (DbType)ConvertDbType(ConnectionType.OleDb, type);
command.Parameters.Add(parameter);
return parameter;
}
else if(command.GetType() == typeof(OracleCommand))
{
OracleParameter parameter;
parameter = new OracleParameter();
parameter.ParameterName = name;
parameter.Direction = direction;
parameter.OracleType = (OracleType)ConvertDbType(ConnectionType.Oracle, type);
command.Parameters.Add(parameter);
return parameter;
}
else if (command.GetType() == typeof(OdbcCommand))
{
OdbcParameter parameter;
parameter = new OdbcParameter();
parameter.ParameterName = name;
parameter.Direction = direction;
parameter.OdbcType = (OdbcType)ConvertDbType(ConnectionType.ODBC, type);
command.Parameters.Add(parameter);
return parameter;
}
else
return null;
}
catch(Exception exc)
{
throw new Exception("Error in MakeParameter", exc);
}
}

public static IDataAdapter MakeDataAdapter(IDbCommand commandSelect)
{
try
{
if(commandSelect.Connection.GetType() == typeof(SqlConnection))
{
SqlDataAdapter adapter;
adapter = new SqlDataAdapter((SqlCommand)commandSelect);
return adapter;
}

else if(commandSelect.Connection.GetType() == typeof(OleDbConnection))
{
OleDbDataAdapter adapter;
adapter = new OleDbDataAdapter((OleDbCommand)commandSelect);
return adapter;
}
else if(commandSelect.Connection.GetType() == typeof(OdbcConnection))
{
OdbcDataAdapter adapter;
adapter = new OdbcDataAdapter((OdbcCommand)commandSelect);
return adapter;
}
else if (commandSelect.Connection.GetType() == typeof(OracleConnection))
{
OracleDataAdapter adapter;
adapter = new OracleDataAdapter((OracleCommand)commandSelect);
return adapter;
}
else
return null;
}
catch(Exception exc)
{
throw new Exception("Error in MakedataAdapter(p_commandSelect)", exc);
}
}

public static IDataAdapter MakeDataAdapter(IDbCommand commandSelect, IDbCommand commandDelete, IDbCommand commandInsert, IDbCommand commandUpdate)
{
try
{
if(commandSelect.Connection.GetType() == typeof(SqlConnection))
{
SqlDataAdapter adapter;
adapter = (SqlDataAdapter)SQLHelper.MakeDataAdapter(commandSelect);
adapter.DeleteCommand = (SqlCommand)commandDelete;
adapter.InsertCommand = (SqlCommand)commandInsert;
adapter.UpdateCommand = (SqlCommand)commandUpdate;
return adapter;
}

else if(commandSelect.Connection.GetType() == typeof(OleDbConnection))
{
OleDbDataAdapter adapter;
adapter = (OleDbDataAdapter)SQLHelper.MakeDataAdapter(commandSelect);
adapter.DeleteCommand = (OleDbCommand)commandDelete;
adapter.InsertCommand = (OleDbCommand)commandInsert;
adapter.UpdateCommand = (OleDbCommand)commandUpdate;
return adapter;
}

else if(commandSelect.Connection.GetType() == typeof(OdbcConnection))
{
OdbcDataAdapter adapter;
adapter = (OdbcDataAdapter)SQLHelper.MakeDataAdapter(commandSelect);
adapter.DeleteCommand = (OdbcCommand)commandDelete;
adapter.InsertCommand = (OdbcCommand)commandInsert;
adapter.UpdateCommand = (OdbcCommand)commandUpdate;
return adapter;
}

else if (commandSelect.Connection.GetType() == typeof(OracleConnection))
{
OracleDataAdapter adapter;
adapter = (OracleDataAdapter)SQLHelper.MakeDataAdapter(commandSelect);
adapter.DeleteCommand = (OracleCommand)commandDelete;
adapter.InsertCommand = (OracleCommand)commandInsert;
adapter.UpdateCommand = (OracleCommand)commandUpdate;
return adapter;
}
else
return null;
}
catch(Exception exc)
{
throw new Exception("Error in MakedataAdapter(commandSelect, commandDelete, commandInsert, commandUpdate)", exc);
}
}

[ssy888]

private static int ConvertDbType(ConnectionType conType, dataType tp)
{
switch (conType)
{
case ConnectionType.SQL:
switch (tp)
{
case dataType.VarChar:
return (int) SqlDbType.VarChar;

case dataType.NVarChar:
return (int) SqlDbType.NVarChar;

case dataType.Int:
return (int) SqlDbType.Int;

case dataType.DateTime:
return (int) SqlDbType.DateTime;

case dataType.Bool:
return (int) SqlDbType.Bit;

case dataType.SmallInt:
return (int) SqlDbType.SmallInt;

case dataType.VarBinary:
return (int) SqlDbType.VarBinary;

case dataType.Money:
return (int) SqlDbType.Money;

case dataType.Dec:
return (int) SqlDbType.Decimal;

case dataType.BigInt:
return (int) SqlDbType.BigInt;

case dataType.Text:
return (int) SqlDbType.Text;

case dataType.CharFixed:
return (int) SqlDbType.Char;
}
break;

case ConnectionType.OleDb:
switch (tp)
{
case dataType.VarChar:
return (int) OleDbType.VarChar;

case dataType.NVarChar:
return (int) OleDbType.VarWChar;

case dataType.Int:
return (int) OleDbType.Integer;

case dataType.DateTime:
return (int) OleDbType.Date;

case dataType.Bool:
return (int) OleDbType.Boolean;

case dataType.SmallInt:
return (int) OleDbType.SmallInt;

case dataType.VarBinary:
return (int) OleDbType.VarBinary;

case dataType.Money:
return (int) OleDbType.Currency;

case dataType.Dec:
return (int) OleDbType.Decimal;

case dataType.BigInt:
return (int) OleDbType.BigInt;

case dataType.Text:
return (int) OleDbType.LongVarChar;

case dataType.CharFixed:
return (int) OleDbType.Char;
}
break;

case ConnectionType.ODBC:
switch (tp)
{
case dataType.VarChar:
return (int) OdbcType.VarChar;

case dataType.NVarChar:
return (int) OdbcType.NVarChar;

case dataType.Int:
return (int) OdbcType.Int;

case dataType.DateTime:
return (int) OdbcType.DateTime;

case dataType.Bool:
return (int) OdbcType.Bit;

case dataType.SmallInt:
return (int) OdbcType.SmallInt;

case dataType.VarBinary:
return (int) OdbcType.VarBinary;

case dataType.Money:
return (int) OdbcType.Decimal;

case dataType.Dec:
return (int) OdbcType.Decimal;

case dataType.BigInt:
return (int) OdbcType.BigInt;

case dataType.Text:
return (int) OdbcType.Text;

case dataType.CharFixed:
return (int) OdbcType.Char;
}
break;

case ConnectionType.Oracle:
switch (tp)
{
case dataType.VarChar:
return (int)OdbcType.VarChar;

case dataType.NVarChar:
return (int)OdbcType.NVarChar;

case dataType.Int:
return (int)OdbcType.Int;

case dataType.DateTime:
return (int)OdbcType.DateTime;

case dataType.Bool:
return (int)OdbcType.Bit;

case dataType.SmallInt:
return (int)OdbcType.SmallInt;

case dataType.VarBinary:
return (int)OdbcType.VarBinary;

case dataType.Money:
return (int)OdbcType.Decimal;

case dataType.Dec:
return (int)OdbcType.Decimal;

case dataType.BigInt:
return (int)OdbcType.BigInt;

case dataType.Text:
return (int)OdbcType.Text;

case dataType.CharFixed:
return (int)OdbcType.Char;
}
break;
}
return -1;
}
}

[ssy888]

上下两部分合起来，oracle的还没试。
等手头工作弄好写个读取数据的例子。

[tz6722033]

头像应该被河蟹。

[chen_ya_ping]

不错，还是顶一个

[wuyq11]

判断太多，实用工厂模式更好

[wanghao3616]

看到case那么多项就晕

[C5662601]

顶

[wanghao3616]

还是觉得 分开好点
access mssql mysql oracle
还是有特殊的函数不能通用

曾经写过asp 支持 access mssql的数据类
发觉 实用性 不高
还是分开 更好

[diandian82]

呵呵

[加油馒头]

感觉有点无用功。。。。

你随便想象就知道，一个项目怎么可能经常换数据库？
换数据库是有代价的。。数据移植，所引发的数据完整性，一致性等等

就算经常换数据库，你的SQL语句难够兼容不同的数据库？
还是要改

而且上面写的语句都是比较死板的，不够灵活。。。

个人的建议。