using
System;
using
System.Collections;
using
System.Collections.Specialized;
using
System.Runtime.Remoting.Messaging;
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
namespace
LTP.SQLServerDAL
{
///
<summary>
///
ADO.NET数据库操作基础类。
///
</summary>
public
abstract
class
DbManagerSQL
{
//
数据库连接字符串
protected
static
string
connectionString
=
ConfigurationSettings.AppSettings[
"
ConnectionString
"
];
public
DbManagerSQL()
{
//
//
TODO: 在此处添加构造函数逻辑
//
}
///
<summary>
///
执行SQL语句,返回影响的记录数
///
</summary>
///
<param name="SQLString"></param>
///
<returns></returns>
public
static
int
ExecuteSql(
string
SQLString)
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
using
(SqlCommand cmd
=
new
SqlCommand(SQLString,connection))
{
try
{
connection.Open();
int
rows
=
cmd.ExecuteNonQuery();
return
rows;
}
catch
(System.Data.SqlClient.SqlException E)
{
throw
new
Exception(E.Message);
}
}
}
}
///
<summary>
///
执行两条SQL语句,实现数据库事务。
///
</summary>
///
<param name="SQLString1"></param>
///
<param name="SQLString2"></param>
public
static
void
ExecuteSqlTran(
string
SQLString1,
string
SQLString2)
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd
=
new
SqlCommand();
cmd.Connection
=
connection;
SqlTransaction tx
=
connection.BeginTransaction();
cmd.Transaction
=
tx;
try
{
cmd.CommandText
=
SQLString1;
cmd.ExecuteNonQuery();
cmd.CommandText
=
SQLString2;
cmd.ExecuteNonQuery();
tx.Commit();
}
catch
(System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw
new
Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///
<summary>
///
执行多条SQL语句,实现数据库事务,每条语句以“;”分割。
///
</summary>
///
<param name="SQLStringList"></param>
public
static
void
ExecuteSqlTran(
string
SQLStringList)
{
using
(OdbcConnection conn
=
new
OdbcConnection(connectionString))
{
conn.Open();
OdbcCommand cmd
=
new
OdbcCommand();
cmd.Connection
=
conn;
OdbcTransaction tx
=
conn.BeginTransaction();
cmd.Transaction
=
tx;
try
{
string
[] split
=
SQLStringList.Split(
new
Char [] {
'
;
'
});
foreach
(
string
strsql
in
split)
{
if
(strsql.Trim()
!=
""
)
{
cmd.CommandText
=
strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch
(System.Data.Odbc.OdbcException E)
{
tx.Rollback();
throw
new
Exception(E.Message);
}
}
}
///
<summary>
///
执行带一个存储过程参数的的SQL语句。
///
</summary>
///
<param name="SQLString"></param>
///
<param name="content"></param>
///
<returns></returns>
public
static
int
ExecuteSql(
string
SQLString,
string
content)
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
SqlCommand cmd
=
new
SqlCommand(SQLString,connection);
System.Data.SqlClient.SqlParameter myParameter
=
new
System.Data.SqlClient.SqlParameter (
"
@content
"
, SqlDbType.NText);
myParameter.Value
=
content ;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int
rows
=
cmd.ExecuteNonQuery();
return
rows;
}
catch
(System.Data.SqlClient.SqlException E)
{
throw
new
Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///
<summary>
///
向数据库里插入图像格式的字段
///
</summary>
///
<param name="strSQL"></param>
///
<param name="fs"></param>
///
<returns></returns>
public
static
int
ExecuteSqlInsertImg(
string
strSQL,
byte
[] fs)
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
SqlCommand cmd
=
new
SqlCommand(strSQL,connection);
System.Data.SqlClient.SqlParameter myParameter
=
new
System.Data.SqlClient.SqlParameter (
"
@fs
"
, SqlDbType.Image);
myParameter.Value
=
fs ;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int
rows
=
cmd.ExecuteNonQuery();
return
rows;
}
catch
(System.Data.SqlClient.SqlException E)
{
throw
new
Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///
<summary>
///
执行一条计算查询结果语句,返回查询结果(整数)。
///
</summary>
///
<param name="strSQL"></param>
///
<returns></returns>
public
static
int
GetCount(
string
strSQL)
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
SqlCommand cmd
=
new
SqlCommand(strSQL,connection);
try
{
connection.Open();
SqlDataReader result
=
cmd.ExecuteReader();
int
i
=
0
;
while
(result.Read())
{
i
=
result.GetInt32(
0
);
}
result.Close();
return
i;
}
catch
(System.Data.SqlClient.SqlException e)
{
throw
new
Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///
<summary>
///
执行一条计算查询结果语句,返回查询结果(object)。
///
</summary>
///
<param name="SQLString"></param>
///
<returns></returns>
public
static
object
GetSingle(
string
SQLString)
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
SqlCommand cmd
=
new
SqlCommand(SQLString,connection);
try
{
connection.Open();
object
obj
=
cmd.ExecuteScalar();
if
((Object.Equals(obj,
null
))
||
(Object.Equals(obj,System.DBNull.Value)))
{
return
null
;
}
else
{
return
obj;
}
}
catch
(System.Data.SqlClient.SqlException e)
{
throw
new
Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///
<summary>
///
执行查询语句,返回SqlDataReader
///
</summary>
///
<param name="strSQL"></param>
///
<returns></returns>
public
static
SqlDataReader ExecuteReader(
string
strSQL)
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
SqlCommand cmd
=
new
SqlCommand(strSQL,connection);
SqlDataReader myReader;
try
{
connection.Open();
myReader
=
cmd.ExecuteReader();
return
myReader;
}
catch
(System.Data.SqlClient.SqlException e)
{
throw
new
Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///
<summary>
///
执行查询语句,返回DataSet
///
</summary>
///
<param name="SQLString"></param>
///
<returns></returns>
public
static
DataSet Query(
string
SQLString)
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
DataSet ds
=
new
DataSet();
try
{
connection.Open();
SqlDataAdapter command
=
new
SqlDataAdapter(SQLString,connection);
command.Fill(ds,
"
ds
"
);
}
catch
(System.Data.SqlClient.SqlException ex)
{
throw
new
Exception(ex.Message);
}
return
ds;
}
}
#region
存储过程操作
///
<summary>
///
运行存储过程
///
</summary>
///
<param name="storedProcName"></param>
///
<param name="parameters"></param>
///
<returns></returns>
public
static
SqlDataReader RunProcedure(
string
storedProcName, IDataParameter[] parameters )
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
SqlDataReader returnReader;
connection.Open();
SqlCommand command
=
BuildQueryCommand( connection,storedProcName, parameters );
command.CommandType
=
CommandType.StoredProcedure;
returnReader
=
command.ExecuteReader();
//
Connection.Close();
return
returnReader;
}
}
private
static
SqlCommand BuildQueryCommand(SqlConnection connection,
string
storedProcName, IDataParameter[] parameters)
{
SqlCommand command
=
new
SqlCommand( storedProcName, connection );
command.CommandType
=
CommandType.StoredProcedure;
foreach
(SqlParameter parameter
in
parameters)
{
command.Parameters.Add( parameter );
}
return
command;
}
public
static
DataSet RunProcedure(
string
storedProcName, IDataParameter[] parameters,
string
tableName )
{
using
(SqlConnection connection
=
new
SqlConnection(connectionString))
{
DataSet dataSet
=
new
DataSet();
connection.Open();
SqlDataAdapter sqlDA
=
new
SqlDataAdapter();
sqlDA.SelectCommand
=
BuildQueryCommand(connection, storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
connection.Close();
return
dataSet;
}
}
#endregion
}
}
--------------------------------------------------------------------------------------------------------------------------------
//
这是我更新的一个版本,与上面可以说变化相当大
//
其中FlashGateway是Flash Remoting的一个dll大家可以去掉相关代码,不影响使用
using
System;
using
System.Collections.Generic;
using
System.Text;
using
System.Collections;
using
System.Collections.Specialized;
using
System.Data;
using
System.Data.SqlClient;
using
System.Drawing;
using
mvi.sysmanage;
using
FlashGateway.IO;
namespace
mvi.dbaccess
{
#region
class sqlcom
///
<summary>
///
sqlcom
///
</summary>
public
class
sqlCom
{
//
FLASHSRV\HIPIHI
//
private string DBCnStr = @"Data Source=FLASHSRV;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=1";
private
string
DBCnStr
=
@"
Data Source=mvi-dpe;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=mvi
"
;
public
sqlCom()
{
//
DBCnStr = @"Data Source=FLASHSRV;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=1";
DBCnStr
=
@"
Data Source=mvi-dpe;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=mvi
"
;
}
public
sqlCom(
string
connectstring)
{
if
(connectstring.Length
>
1
)
{
DBCnStr
=
connectstring;
}
}
public
string
SQLCnStr
{
get
{
return
DBCnStr;
}
set
{
DBCnStr
=
value;
}
}
#region
InitSqlConnection 初始化Sql连接字符串
///
<summary>
///
初始化Sql连接字符串
///
</summary>
///
<param name="DBCnStr">
传入的dbconnection
</param>
///
<param name="cmdText">
传入的cmd text
</param>
///
<returns>
sql server connection string
</returns>
private
string
InitSqlConnection(
string
DBCnStr,
string
cmdText)
{
//
temp code
return
DBCnStr;
///
/ final code
//
int iD = DBCnStr.IndexOf("Data Source=");
//
int iL = DBCnStr.Substring(iD + 12).Split(';')[0].Length;
//
string strSqlServerName = DBCnStr.Substring(iD + 12, iL);
//
string strNewSqlServerName = GetSqlServerName(cmdText);
//
return DBCnStr.Replace(strSqlServerName, strNewSqlServerName);
}
#endregion
#region
GetSqlServerName 由sql string 获取数据库服务器名
///
<summary>
///
由sql string 获取sql server name
///
</summary>
///
<param name="cmdText">
传入的cmd text
</param>
///
<returns>
sql server name
</returns>
private
string
GetSqlServerName(
string
cmdText)
{
return
cmdText.Substring(cmdText.IndexOf(
"
from
"
)
+
5
).Split(
'
.
'
)[
0
].ToString();
}
#endregion
# region GetDataSet 通过执行SQL语句返回一个状态
///
<summary>
///
通过执行SQL语句返回一个状态
///
</summary>
///
<param name="cmdText">
“SQL 文本”
</param>
///
<param name="oCn">
"连接对象"
</param>
///
<param name="oDs">
"引用的DataSet它将在程序中改变内容"
</param>
///
<returns>
"成功则返回0,否则返回错误代码"
</returns>
public
int
GetDataSet(
string
cmdText,
ref
DataSet oDs)
{
DBCnStr
=
this
.InitSqlConnection(DBCnStr, cmdText);
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
//
定义数据适配对象
SqlDataAdapter oleDataAdapter
=
new
SqlDataAdapter(cmdText, oCn);
int
status
=
-
1
;
try
{
//
填充DataSet
oleDataAdapter.Fill(oDs);
status
=
0
;
}
catch
(Exception oErr)
{
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
oleDataAdapter
=
null
;
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
}
return
status;
}
#endregion
# region GetDataTable 执行SQL语句并返回一个表
///
<summary>
///
执行SQL语句并返回一个表
///
</summary>
///
<param name="cmdText">
SQL文本
</param>
///
<param name="DBCnStr">
dbconnect
</param>
///
<param name="inDt">
返回表
</param>
///
<returns>
成功则返回0,否则返回错误代码
</returns>
public
int
GetDataTable(
string
cmdText,
ref
DataTable inDt)
{
DBCnStr
=
this
.InitSqlConnection(DBCnStr, cmdText);
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
//
建立数据适配对象
SqlDataAdapter oleDataAdapter
=
new
SqlDataAdapter(cmdText, oCn);
int
status
=
-
1
;
try
{
//
填充数据表
oleDataAdapter.Fill(inDt);
status
=
0
;
}
catch
(Exception oErr)
{
//
异常处理
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
oleDataAdapter
=
null
;
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
}
return
status;
}
#endregion
# region GetDataTable 执行SQL语句并返回一个表
///
<summary>
///
执行SQL语句并返回一个表
///
</summary>
///
<param name="cmdText">
SQL文本
</param>
///
<param name="DBCnStr">
dbconnect
</param>
///
<param name="inDt">
返回表
</param>
///
<returns>
成功则返回0,否则返回错误代码
</returns>
public
int
GetCount(
string
cmdText)
{
DBCnStr
=
this
.InitSqlConnection(DBCnStr, cmdText);
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
//
建立数据适配对象
SqlDataAdapter oleDataAdapter
=
new
SqlDataAdapter(cmdText, oCn);
DataTable inDt
=
new
DataTable();
int
status
=
-
1
;
try
{
//
填充数据表
oleDataAdapter.Fill(inDt);
status
=
inDt.Rows.Count;
}
catch
(Exception oErr)
{
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
oleDataAdapter
=
null
;
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
}
return
status;
}
#endregion
# region
//
GetNVColl 执行SQL语句并返回NameValueCollection
///
//
<summary>
///
// 执行SQL语句并返回NameValueCollection
///
//
</summary>
///
//
<param name="cmdText">
SQL文本
</param>
///
//
<param name="NameValueCollection">
nvColl
</param>
///
//
<returns>
成功则返回0,否则返回错误代码
</returns>
//
public int GetNVColl(string cmdText, ref NameValueCollection nvColl)
//
{
//
DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);
//
SqlConnection oCn = new SqlConnection(DBCnStr);
//
try
//
{
//
oCn.Open();
//
}
//
catch (Exception oErr)
//
{
//
//
WriteFile(oErr.Message);
//
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
//
return -1;
//
}
//
//
建立数据读取对象
//
SqlCommand oleCommand = new SqlCommand(cmdText, oCn);
//
//
填充SqlDataReader
//
SqlDataReader oleReader;
//
int status = -1;
//
try
//
{
//
oleReader = oleCommand.ExecuteReader();
//
//
Always call Read before accessing data.
//
while (oleReader.Read())
//
{
//
for (int i = 0; i < oleReader.FieldCount; i++)
//
{
//
if (oleReader.GetValue(i).ToString() != "")
//
nvColl.Add(oleReader.GetName(i), oleReader.GetString(i));
//
}
//
}
//
status = 0;
//
}
//
catch (Exception oErr)
//
{
//
//
异常处理
//
//
WriteFile(oErr.Message);
//
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
//
status = -1;
//
}
//
finally
//
{
//
oleReader = null;
//
if (oCn.State == System.Data.ConnectionState.Open)
//
{
//
oCn.Close();
//
}
//
}
//
return status;
//
}
#endregion
//
#region
GetArrayList 执行SQL语句并返回一个ArrayList
///
//
<summary>
///
// 执行SQL语句并返回一个ArrayList
///
//
</summary>
///
//
<param name="cmdText">
SQL文本
</param>
///
//
<returns>
返回ArrayList arraylist[i]为name,arraylist[i+1]为value
</returns>
//
public ArrayList GetArrayList(string cmdText, ref ArrayList aName, ref ArrayList aValue)
//
{
//
ArrayList aNameValue = new ArrayList();
//
SqlConnection oCn = new SqlConnection(DBCnStr);
//
try
//
{
//
oCn.Open();
//
}
//
catch (Exception oErr)
//
{
//
//
WriteFile(oErr.Message);
//
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
//
return null;
//
}
//
//
建立数据读取对象
//
SqlCommand oleCommand = new SqlCommand(cmdText, oCn);
//
//
填充SqlDataReader
//
SqlDataReader oleReader;
//
ArrayList status = null;
//
try
//
{
//
oleReader = oleCommand.ExecuteReader();
//
//
Always call Read before accessing data.
//
while (oleReader.Read())
//
{
//
for (int i = 0; i < oleReader.FieldCount; i++)
//
{
//
if (oleReader.GetValue(i).ToString() != "")
//
aName.Add(oleReader.GetName(i));
//
aValue.Add(oleReader.GetString(i + 1));
//
}
//
}
//
status = aValue;
//
}
//
catch (Exception oErr)
//
{
//
//
异常处理
//
//
WriteFile(oErr.Message);
//
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
//
status = null;
//
}
//
finally
//
{
//
oleReader = null;
//
if (oCn.State == System.Data.ConnectionState.Open)
//
{
//
oCn.Close();
//
}
//
}
//
return status;
//
}
#endregion
//
#region
GetArrayList 执行SQL语句并返回一个ArrayList
///
<summary>
///
执行SQL语句并返回一个ArrayList
///
</summary>
///
<param name="cmdText">
SQL文本
</param>
///
<returns>
返回ArrayList arraylist[i]为name,arraylist[i+1]为value
</returns>
public
int
GetArrayList(
string
cmdText,
ref
ArrayList aNameValue)
{
DBCnStr
=
this
.InitSqlConnection(DBCnStr, cmdText);
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
//
建立数据读取对象
SqlCommand oleCommand
=
new
SqlCommand(cmdText, oCn);
//
填充SqlDataReader
SqlDataReader oleReader;
int
status
=
-
1
;
try
{
oleReader
=
oleCommand.ExecuteReader();
//
Always call Read before accessing data.
while
(oleReader.Read())
{
for
(
int
i
=
0
; i
<
oleReader.FieldCount
-
1
; i
++
)
{
if
(oleReader.GetValue(i).ToString()
!=
""
)
aNameValue.Add(oleReader.GetName(i));
}
}
status
=
1
;
}
catch
(Exception oErr)
{
//
异常处理
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
oleReader
=
null
;
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
}
return
status;
}
#endregion
#region
GetASObject 执行SQL语句并返回一个包含多条数据的ASObject (name,value)
///
<summary>
///
执行SQL语句,查询两个字段,并返回一个ASObject
///
</summary>
///
<param name="cmdText">
SQL文本
</param>
///
<param name="asO">
ASObject 对象
</param>
///
<returns>
返回int ASObject[i]为(name,value)
</returns>
public
int
GetASObjectMulti(
string
cmdText,
ref
ASObject asO)
{
DBCnStr
=
this
.InitSqlConnection(DBCnStr, cmdText);
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
//
建立数据读取对象
SqlCommand oleCommand
=
new
SqlCommand(cmdText, oCn);
//
填充SqlDataReader
SqlDataReader oleReader;
int
status
=
-
1
;
try
{
int
i
=
1
;
oleReader
=
oleCommand.ExecuteReader();
//
Always call Read before accessing data.
while
(oleReader.Read())
{
for
(
int
j
=
0
; j
<
oleReader.FieldCount; j
++
)
{
asO.Add(i
+
"
@
"
+
oleReader.GetName(j),oleReader.GetValue(j));
//
i@+"name",i为第几条数据的序号
}
i
++
;
}
status
=
1
;
}
catch
(Exception oErr)
{
//
异常处理
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
oleReader
=
null
;
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
}
return
status;
}
#endregion
#region
GetASObjectSingle 执行SQL语句并返回一个包含单条数据的ASObject (name,value)
///
<summary>
///
执行SQL语句查询一条数据(必须返回一条数据),返回一个ASObject
///
</summary>
///
<param name="cmdText">
SQL文本
</param>
///
<param name="asO">
ASObject 对象
</param>
///
<returns>
返回int ASObject[i]为(name,value)
</returns>
public
int
GetASObjectSingle(
string
cmdText,
ref
ASObject asO)
{
DBCnStr
=
this
.InitSqlConnection(DBCnStr, cmdText);
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
//
建立数据读取对象
SqlCommand oleCommand
=
new
SqlCommand(cmdText, oCn);
//
填充SqlDataReader
SqlDataReader oleReader;
int
status
=
-
1
;
try
{
//
oleReader = oleCommand.ExecuteScalar();
//
modified by apenni 2006-5-6
oleReader
=
oleCommand.ExecuteReader();
//
Always call Read before accessing data.
while
(oleReader.Read())
{
for
(
int
i
=
0
; i
<
oleReader.FieldCount; i
++
)
{
asO.Add(oleReader.GetName(i), oleReader.GetValue(i));
}
}
status
=
1
;
}
catch
(Exception oErr)
{
//
异常处理
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
oleReader
=
null
;
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
}
return
status;
}
#endregion
#region
ExecuteSql 执行SET,DELETE语句时返回影响的行数
///
<summary>
///
执行SET,DELETE语句时返回影响的行数
///
</summary>
///
<param name="cmdText">
“SQL文本”
</param>
///
<returns>
“返回影响的行数,否则返回错误代码”
</returns>
public
int
ExecuteSql(
string
cmdText)
{
int
intReturn
=
-
1
;
//
返回影响的行数。
SqlCommand oCmd
=
new
SqlCommand();
DBCnStr
=
this
.InitSqlConnection(DBCnStr, cmdText);
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
oCmd.Connection
=
oCn;
oCmd.CommandType
=
CommandType.Text;
oCmd.CommandText
=
cmdText;
//
定义事务 设定隔离级别
SqlTransaction oTx
=
oCn.BeginTransaction(IsolationLevel.ReadCommitted);
oCmd.Transaction
=
oTx;
//
处理SQL语句
#region
事务处理
try
{
//
支持事务
intReturn
=
oCmd.ExecuteNonQuery();
oTx.Commit();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
oTx.Rollback();
intReturn
=
-
1
;
}
finally
{
oCmd
=
null
;
oTx
=
null
;
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
}
#endregion
return
intReturn;
}
#endregion
#region
ExecuteSql 执行SET,DELETE语句时返回影响的行数
///
<summary>
///
执行SET,DELETE语句时返回影响的行数
///
</summary>
///
<param name="cmdText">
“SQL文本,支持多sql语句通过';'拼接”
</param>
///
<returns>
“返回影响的行数,否则返回错误代码”
</returns>
public
int
ExecuteSql(
params
string
[] cmdText)
{
string
strSql
=
string
.Empty;
foreach
(
string
strCmd
in
cmdText)
{
strSql
+=
strCmd;
}
return
ExecuteSql(strSql);
}
//
added by apenni 2006-5-6
#endregion
#region
CallStoreProc 调用系统存储过程返回一个整数
///
<summary>
///
调用系统存储过程返回一个整数
///
</summary>
///
<param name = "strSysSPName">
“存储过程枚举类型”
</param>
///
<param name="InParaName">
"in参数名字"
</param>
///
<param name = "ParamValue">
“参数列表”
</param>
///
<param name="OutParaName">
"out参数名字"
</param>
///
<param name="OutParaValue">
"返回的参数值"
</param>
///
<param name="IType">
"out参数的类型"
</param>
///
<returns>
"成功则返回所影响的行数,否则返回-1"
</returns>
public
int
CallStoreProc(
string
strSysSPName, IList InParaName, IList InParamValue, IList OutParaName,
ref
object
[] OutParaValue, DBTYPE[] IType)
{
int
inReturn
=
-
1
;
SqlCommand oCmd
=
new
SqlCommand();
oCmd.CommandText
=
strSysSPName;
oCmd.CommandType
=
CommandType.StoredProcedure;
#region
in参数的建立
if
(InParamValue
!=
null
&&
InParaName
!=
null
)
{
//
建立in参数
for
(
int
i
=
0
; i
<
InParamValue.Count; i
++
)
{
SqlParameter oPara
=
new
SqlParameter();
//
调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
if
(InParaName[i]
!=
null
)
{
oPara
=
SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);
oPara.Direction
=
ParameterDirection.Input;
oCmd.Parameters.Add(oPara);
}
}
}
#endregion
#region
out参数的建立
if
(OutParaName
!=
null
&&
OutParaValue
!=
null
&&
IType
!=
null
)
{
//
建立in参数
for
(
int
i
=
0
; i
<
OutParaName.Count; i
++
)
{
SqlParameter oPara
=
new
SqlParameter();
//
调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
if
(OutParaName[i]
!=
null
)
{
oPara
=
SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());
oPara.Direction
=
ParameterDirection.Output;
oCmd.Parameters.Add(oPara);
}
}
}
#endregion
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
oCmd.Connection
=
oCn;
//
连接数据库和执行存储过程
try
{
inReturn
=
oCmd.ExecuteNonQuery();
for
(
int
i
=
0
; i
<
OutParaValue.Length; i
++
)
{
OutParaValue[i]
=
oCmd.Parameters[OutParaName[i].ToString()].Value;
}
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
inReturn
=
-
1
;
}
finally
{
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
oCmd
=
null
;
}
return
inReturn;
}
#endregion
#region
CallStoreProc 调用系统存储过程并影响生成一个object对象值
///
<summary>
///
调用系统存储过程并影响生成一个object对象值
///
</summary>
///
<param name = "strSysSPName">
“存储过程枚举类型”
</param>
///
<param name="InParaName">
"in参数名字"
</param>
///
<param name = "InParamValue">
“in参数列表”
</param>
///
<param name="OutParaName">
"out参数名字"
</param>
///
<param name="OutParaValue">
"out参数值"
</param>
///
<param name="IType">
"out参数的类型"
</param>
///
<param name="inObject">
"引用的值"
</param>
///
<returns>
成功则返回1,否则返回-1或错误代码
</returns>
public
int
CallStoreProc(
string
strSysSPName, IList InParaName, IList InParamValue, IList OutParaName,
ref
object
[] OutParaValue, DBTYPE[] IType,
ref
object
objReturn)
{
//
建立Command对象
SqlCommand oCmd
=
new
SqlCommand();
oCmd.CommandText
=
strSysSPName.ToString();
oCmd.CommandType
=
CommandType.StoredProcedure;
int
status
=
-
1
;
#region
in参数的建立
if
(InParamValue
!=
null
&&
InParaName
!=
null
)
{
//
建立in参数
for
(
int
i
=
0
; i
<
InParamValue.Count; i
++
)
{
SqlParameter oPara
=
new
SqlParameter();
//
调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
if
(InParaName[i]
!=
null
)
{
oPara
=
SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);
oPara.Direction
=
ParameterDirection.Input;
oCmd.Parameters.Add(oPara);
}
}
}
#endregion
#region
out参数的建立
if
(OutParaName
!=
null
&&
OutParaValue
!=
null
&&
IType
!=
null
)
{
//
建立in参数
for
(
int
i
=
0
; i
<
OutParaName.Count; i
++
)
{
SqlParameter oPara
=
new
SqlParameter();
//
调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
if
(OutParaName[i]
!=
null
)
{
oPara
=
SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());
oPara.Direction
=
ParameterDirection.Output;
oCmd.Parameters.Add(oPara);
}
}
}
#endregion
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
oCmd.Connection
=
oCn;
//
连接数据库和执行存储过程
try
{
//
通过SqlDataAdapter来填充Table
objReturn
=
oCmd.ExecuteScalar();
#region
取得返回参数的值
for
(
int
i
=
0
; i
<
OutParaValue.Length; i
++
)
{
OutParaValue[i]
=
oCmd.Parameters[OutParaName[i].ToString()].Value;
}
#endregion
status
=
0
;
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
oCmd
=
null
;
}
return
status;
}
#endregion
#region
CallStoreProc 调用用户存储过程返回一个DataTable(Select 语句)
///
<summary>
///
调用用户存储过程返回一个DataTable(Select 语句)
///
</summary>
///
<param name = "strSPName">
“存储过程名”
</param>
///
<param name="InParaName">
"in参数名字"
</param>
///
<param name = "InParamValue">
“in参数列表”
</param>
///
<param name="OutParaName">
"out参数名字"
</param>
///
<param name="IType">
"out参数的类型"
</param>
///
<param name="OutParaValue">
"out参数值"
</param>
///
<param name="oDT">
"传入的DataTable引用"
</param>
///
<returns>
"成功则返回1,否则返回-1或错误代码"
</returns>
public
int
CallStoreProc(
string
strSPName, IList InParaName, IList InParamValue, IList OutParaName,
ref
object
[] OutParaValue, DBTYPE[] IType,
ref
DataTable oDT)
{
//
建立Command对象
SqlCommand oCmd
=
new
SqlCommand();
oCmd.CommandText
=
strSPName.ToString();
oCmd.CommandType
=
CommandType.StoredProcedure;
int
status
=
-
1
;
#region
in参数的建立
if
(InParamValue
!=
null
&&
InParaName
!=
null
)
{
//
建立in参数
for
(
int
i
=
0
; i
<
InParamValue.Count; i
++
)
{
SqlParameter oPara
=
new
SqlParameter();
//
调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
if
(InParaName[i]
!=
null
)
{
oPara
=
SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);
oPara.Direction
=
ParameterDirection.Input;
oCmd.Parameters.Add(oPara);
}
}
}
#endregion
#region
out参数的建立
if
(OutParaName
!=
null
&&
OutParaValue
!=
null
&&
IType
!=
null
)
{
//
建立in参数
for
(
int
i
=
0
; i
<
OutParaName.Count; i
++
)
{
SqlParameter oPara
=
new
SqlParameter();
//
调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
if
(OutParaName[i]
!=
null
)
{
oPara
=
SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());
oPara.Direction
=
ParameterDirection.Output;
oCmd.Parameters.Add(oPara);
}
}
}
#endregion
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
oCmd.Connection
=
oCn;
//
连接数据库和执行存储过程
try
{
//
通过SqlDataAdapter来填充Table
SqlDataAdapter oDp
=
new
SqlDataAdapter(oCmd.CommandText.ToString(), oCn);
//
建立SqlDataAdapter与SqlCommand的连接
oDp.SelectCommand
=
oCmd;
oDp.DeleteCommand
=
oCmd;
oDp.UpdateCommand
=
oCmd;
oDp.DeleteCommand
=
oCmd;
//
填充DataTable
oDp.Fill(oDT);
#region
取得返回参数的值
for
(
int
i
=
0
; i
<
OutParaValue.Length; i
++
)
{
OutParaValue[i]
=
oCmd.Parameters[OutParaName[i].ToString()].Value;
}
#endregion
status
=
0
;
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
oCmd
=
null
;
}
return
status;
}
#endregion
#region
CallStoreProc 调用系统存储过程并影响生成一个DataSet对象
///
<summary>
///
调用系统存储过程并影响生成一个DataSet对象
///
</summary>
///
<param name = "strSysSPName">
“存储过程枚举类型”
</param>
///
<param name="InParaName">
"in参数名字"
</param>
///
<param name = "InParamValue">
“in参数列表”
</param>
///
<param name="OutParaName">
"out参数名字"
</param>
///
<param name="OutParaValue">
"out参数值"
</param>
///
<param name="IType">
"out参数的类型"
</param>
///
<param name=" oDs">
"引用的DataSet"
</param>
///
<returns>
成功则返回1,否则返回-1或错误代码
</returns>
public
int
CallStoreProc(
string
strSysSPName, IList InParaName, IList InParamValue, IList OutParaName,
ref
object
[] OutParaValue, DBTYPE[] IType,
ref
DataSet oDs)
{
SqlCommand oCmd
=
new
SqlCommand();
oCmd.CommandText
=
strSysSPName;
oCmd.CommandType
=
CommandType.StoredProcedure;
int
status
=
-
1
;
#region
in参数的建立
if
(InParamValue
!=
null
&&
InParaName
!=
null
)
{
//
建立in参数
for
(
int
i
=
0
; i
<
InParamValue.Count; i
++
)
{
SqlParameter oPara
=
new
SqlParameter();
//
调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
if
(InParaName[i]
!=
null
)
{
oPara
=
SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);
oPara.Direction
=
ParameterDirection.Input;
oCmd.Parameters.Add(oPara);
}
}
}
#endregion
#region
out参数的建立
if
(OutParaName
!=
null
&&
OutParaValue
!=
null
&&
IType
!=
null
)
{
//
建立in参数
for
(
int
i
=
0
; i
<
OutParaName.Count; i
++
)
{
SqlParameter oPara
=
new
SqlParameter();
//
调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数
if
(OutParaName[i]
!=
null
)
{
oPara
=
SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());
oPara.Direction
=
ParameterDirection.Output;
oCmd.Parameters.Add(oPara);
}
}
}
#endregion
SqlConnection oCn
=
new
SqlConnection(DBCnStr);
try
{
oCn.Open();
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
return
-
1
;
}
oCmd.Connection
=
oCn;
//
连接数据库和执行存储过程
try
{
//
通过SqlDataAdapter来填充Table
SqlDataAdapter oDp
=
new
SqlDataAdapter(oCmd.CommandText.ToString(), oCn);
//
建立SqlDataAdapter与SqlCommand的连接
oDp.SelectCommand
=
oCmd;
oDp.DeleteCommand
=
oCmd;
oDp.UpdateCommand
=
oCmd;
oDp.DeleteCommand
=
oCmd;
//
oDp.Fill(oDs);
#region
取得返回参数的值
for
(
int
i
=
0
; i
<
OutParaValue.Length; i
++
)
{
OutParaValue[i]
=
oCmd.Parameters[OutParaName[i].ToString()].Value;
}
#endregion
status
=
0
;
}
catch
(Exception oErr)
{
//
WriteFile(oErr.Message);
Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);
status
=
-
1
;
}
finally
{
if
(oCn.State
==
System.Data.ConnectionState.Open)
{
oCn.Close();
}
oCmd
=
null
;
}
return
status;
}
#endregion
#region
GetSqlWhere 产生SQL语句
///
<summary>
///
产生SQL语句
///
</summary>
///
<param name="InName">
表字段名
</param>
///
<param name="InValue">
表字段值
</param>
///
<returns>
结果SQL语句
</returns>
public
string
GetSqlWhere(ArrayList InName, ArrayList InValue)
{
DataTable DataTableTmp
=
new
DataTable();
string
StrSqlWhereTmp
=
""
;
string
StrTmp
=
""
;
string
StrName
=
""
;
string
StrValue
=
""
;
if
(InName
==
null
||
InValue
==
null
)
{
return
null
;
}
for
(
int
i
=
0
; i
<
InName.Count; i
++
)
{
StrTmp
=
InName[i].ToString();
if
(StrTmp.Substring(
0
,
2
)
==
"
#S
"
)
//
开始时间
{
StrName
=
StrTmp.Substring(
2
)
+
"
>=
"
;
StrValue
=
"
to_date('
"
+
InValue[i].ToString()
+
"
','yyyy-mm-dd HH24:Mi:ss')
"
;
}
else
if
(StrTmp.Substring(
0
,
2
)
==
"
#E
"
)
//
结束时间
{
StrName
=
StrTmp.Substring(
2
)
+
"
<
"
;
StrValue
=
"
to_date('
"
+
InValue[i].ToString()
+
"
','yyyy-mm-dd HH24:Mi:ss')
"
;
}
else
if
(StrTmp.Substring(
0
,
2
)
==
"
#N
"
)
//
<>条件
{
StrName
=
StrTmp.Substring(
2
)
+
"
<>
"
;
StrValue
=
InValue[i].ToString();
}
else
if
(StrTmp.Substring(
0
,
2
)
==
"
#D
"
)
//
大于条件
{
StrName
=
StrTmp.Substring(
2
)
+
"
>
"
;
StrValue
=
InValue[i].ToString();
}
else
if
(StrTmp.Substring(
0
,
2
)
==
"
#X
"
)
//
小于条件
{
StrName
=
StrTmp.Substring(
2
)
+
"
<
"
;
StrValue
=
InValue[i].ToString();
}
else
if
(StrTmp.Substring(
0
,
2
)
==
"
#I
"
)
//
IN条件
{
StrName
=
StrTmp.Substring(
2
)
+
"
IN (
"
;
StrValue
=
InValue[i].ToString()
+
"
)
"
;
}
else
if
(StrTmp.Substring(
0
,
2
)
==
"
#0
"
)
//
没有条件
{
return
InValue[i].ToString();
}
else
//
等于条件
{
StrName
=
StrTmp
+
"
=
"
;
StrValue
=
InValue[i].ToString();
}
StrSqlWhereTmp
=
StrSqlWhereTmp
+
StrName
+
StrValue
+
"
and
"
;
}
StrSqlWhereTmp
=
StrSqlWhereTmp.Substring(
0
, StrSqlWhereTmp.Length
-
5
);
return
StrSqlWhereTmp;
}
#endregion
}
#endregion
#region
class SQLParamHelper
///
<summary>
///
SQLParamHelper
///
</summary>
internal
class
SQLParamHelper
{
#region
创建出入参数
///
<summary>
///
根据输入的OBJECT对象生成不同的参数
///
</summary>
///
<param name="name">
“参数名字”
</param>
///
<param name="nValue">
“参数值”
</param>
///
<returns></returns>
public
static
SqlParameter CreateParameterWithValue(
string
name,
object
nValue)
{
string
strType;
SqlParameter param;
int
intLenth
=
0
;
if
(nValue
!=
null
)
{
strType
=
nValue.GetType().ToString();
intLenth
=
nValue.ToString().Trim().Length;
if
(intLenth
>
0
)
{
switch
(strType)
{
case
"
System.Int32
"
:
{
param
=
new
SqlParameter(name, SqlDbType.BigInt, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Double
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Decimal);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Single
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Float);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.UInt64
"
:
{
param
=
new
SqlParameter(name, SqlDbType.BigInt, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Int64
"
:
{
param
=
new
SqlParameter(name, SqlDbType.BigInt, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Decimal
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Decimal, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Object
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Real, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.UInt16
"
:
{
param
=
new
SqlParameter(name, SqlDbType.BigInt, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Int16
"
:
{
param
=
new
SqlParameter(name, SqlDbType.BigInt, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Boolean
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Binary);
param.Direction
=
ParameterDirection.Input;
bool
bolTemp
=
(
bool
)nValue;
param.Value
=
(bolTemp
==
true
?
1
:
0
);
break
;
}
case
"
System.String
"
:
{
param
=
new
SqlParameter(name, SqlDbType.VarChar, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.DateTime
"
:
{
param
=
new
SqlParameter(name, SqlDbType.DateTime, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Char
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Char, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.SByte
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Bit, intLenth);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System_XMl
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Xml,
300
);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
case
"
System.Text
"
:
{
if
(intLenth
<
2000
)
{
param
=
new
SqlParameter(name, SqlDbType.Text, intLenth);
}
else
{
param
=
new
SqlParameter(name, SqlDbType.Text);
}
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
default
:
{
param
=
new
SqlParameter(name, SqlDbType.Variant);
param.Direction
=
ParameterDirection.Input;
param.Value
=
nValue;
break
;
}
}
param.Direction
=
ParameterDirection.Input;
}
else
{
param
=
new
SqlParameter(name, SqlDbType.VarChar,
10
);
param.Direction
=
ParameterDirection.Input;
param.Value
=
""
;
}
}
else
{
param
=
new
SqlParameter(name, SqlDbType.Variant);
param.Direction
=
ParameterDirection.Input;
param.Value
=
null
;
}
return
param;
}
#endregion
#region
CreateOutParameterWithValue 建立输出参数
///
<summary>
///
建立输出参数
///
</summary>
///
<param name="name">
"参数名"
</param>
///
<param name="objType">
"参数类型"
</param>
///
<returns></returns>
public
static
SqlParameter CreateOutParameterWithValue(
string
name,
string
objType)
{
string
strType
=
objType;
SqlParameter param;
switch
(strType)
{
case
"
System_Object
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Variant);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Single
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Float);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_UInt64
"
:
{
param
=
new
SqlParameter(name, SqlDbType.BigInt);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Int64
"
:
{
param
=
new
SqlParameter(name, SqlDbType.BigInt);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Int32
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Int);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_UInt16
"
:
{
param
=
new
SqlParameter(name, SqlDbType.SmallInt);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Int16
"
:
{
param
=
new
SqlParameter(name, SqlDbType.SmallInt);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Double
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Float);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Decimal
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Decimal);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Boolean
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Binary);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_String
"
:
{
param
=
new
SqlParameter(name, SqlDbType.VarChar,
200
);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_DateTime
"
:
{
param
=
new
SqlParameter(name, SqlDbType.DateTime);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Char
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Char,
100
);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_SByte
"
:
{
param
=
new
SqlParameter(name, SqlDbType.NChar,
30
);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_Text
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Text,
300
);
param.Direction
=
ParameterDirection.Output;
break
;
}
case
"
System_XMl
"
:
{
param
=
new
SqlParameter(name, SqlDbType.Xml,
300
);
param.Direction
=
ParameterDirection.Output;
break
;
}
default
:
{
param
=
new
SqlParameter(name, SqlDbType.Variant);
param.Direction
=
ParameterDirection.Output;
break
;
}
}
return
param;
}
#endregion
CreateOutParams
#region
CreateParameter 创建形式参数
///
<summary>
///
转换参数为SQL语句的表达式
///
</summary>
///
<param name="nValue">
传入的Object类型值
</param>
///
<returns>
已经转换好的String
</returns>
public
static
string
CreateParameter(SqlParameter oValue)
{
string
strTemPara;
object
oPara_Value
=
oValue.Value;
if
(oPara_Value
!=
null
)
{
string
strType
=
oValue.SqlDbType.ToString();
switch
(strType)
{
case
"
VarChar
"
:
{
strTemPara
=
"
'
"
+
CheckMark(oPara_Value)
+
"
'
"
;
break
;
}
case
"
Char
"
:
{
strTemPara
=
"
'
"
+
CheckMark(oPara_Value)
+
"
'
"
;
break
;
}
case
"
NChar
"
:
{
strTemPara
=
"
'
"
+
CheckMark(oPara_Value)
+
"
'
"
;
break
;
}
case
"
NVarChar
"
:
{
strTemPara
=
"
'
"
+
CheckMark(oPara_Value)
+
"
'
"
;
break
;
}
//
日期型
case
"
DateTime
"
:
{
DateTime dt
=
new
DateTime();
dt
=
(DateTime)oPara_Value;
string
strTP
=
"
'
"
+
dt.Year
+
"
-
"
+
dt.Month
+
"
-
"
+
dt.Day;
strTP
+=
"
"
+
dt.Hour.ToString()
+
"
:
"
+
dt.Minute.ToString();
strTP
+=
"
:
"
+
dt.Second.ToString()
+
"
',
"
;
strTemPara
=
"
TO_DATE(
"
+
strTP
+
"
'yyyy-mm-dd hh24:mi:ss'
"
+
"
)
"
;
break
;
}
case
"
LongVarChar
"
:
{
strTemPara
=
"
'
"
+
CheckMark(oPara_Value)
+
"
'
"
;
break
;
}
case
"
Clob
"
:
{
strTemPara
=
"
'
"
+
CheckMark(oPara_Value)
+
"
'
"
;
break
;
}
default
:
{
strTemPara
=
oPara_Value.ToString();
break
;
}
}
}
else
{
//
将null传入
strTemPara
=
"
null
"
;
}
return
strTemPara;
}
#endregion
#region
CheckMark 替换object的'为''并转换为String
///
<summary>
///
替换object的'为''并转换为String
///
</summary>
///
<param name="objIn">
传入的Object类型
</param>
///
<returns>
已经替换'为''的String
</returns>
private
static
string
CheckMark(
object
objIn)
{
string
strTmp
=
objIn.ToString();
return
strTmp.Replace(
"
'
"
,
"
''
"
);
//
modified by apenni 06.01.02
//
string strRet = "";
//
for (int i = 0; i < strTmp.Length; i++)
//
{
//
if (strTmp[i].ToString() == "'")
//
{
//
strRet += "''";
//
}
//
else
//
{
//
strRet += strTmp[i].ToString();
//
}
//
}
//
return strRet;
}
#endregion
}
#endregion