public static void AddParameter(this System.Data.IDbCommand dbCommand, string name, object value)
{
System.Data.IDbDataParameter parameter = dbCommand.CreateParameter();
parameter.ParameterName = name;
parameter.Value = value;
dbCommand.Parameters.Add(parameter);
}
public static void AddParameter(this System.Data.IDbCommand command, object value)
{
var parameter = command.CreateParameter();
parameter.Value = value;
command.Parameters.Add(parameter);
}
} // End Function AddParameter
public System.Data.IDbDataParameter AddParameter(System.Data.IDbCommand command, string strParameterName, object objValue, System.Data.ParameterDirection pad, System.Data.DbType dbType)
{
System.Data.IDbDataParameter parameter = command.CreateParameter();
if (!strParameterName.StartsWith("@"))
{
strParameterName = "@" + strParameterName;
} // End if (!strParameterName.StartsWith("@"))
parameter.ParameterName = strParameterName;
parameter.DbType = dbType;
parameter.Direction = pad;
// Es ist keine Zuordnung von DbType UInt64 zu einem bekannten SqlDbType vorhanden.
// No association DbType UInt64 to a known SqlDbType
if (objValue == null)
{
parameter.Value = System.DBNull.Value;
}
else
{
parameter.Value = objValue;
}
command.Parameters.Add(parameter);
return(parameter);
} // End Function AddParameter
public static System.Data.IDbDataParameter AddParameter(System.Data.IDbCommand command, string strParameterName,
object objValue, System.Data.ParameterDirection pad, System.Data.DbType dbType)
{
System.Data.IDbDataParameter parameter = command.CreateParameter();
if (!strParameterName.StartsWith("@"))
{
strParameterName = "@" + strParameterName;
}
if ((command.Parameters.Contains(strParameterName)))
{
command.Parameters.RemoveAt(strParameterName);
}
parameter.ParameterName = strParameterName;
parameter.DbType = dbType;
parameter.Direction = pad;
if (objValue == null)
{
parameter.Value = System.DBNull.Value;
}
else
{
parameter.Value = objValue;
}
command.Parameters.Add(parameter);
return(parameter);
}
public static void AddParameters(this System.Data.IDbCommand self, int count)
{
for (var i = 0; i < count; i++)
{
self.Parameters.Add(self.CreateParameter());
}
}
public virtual System.Data.IDbDataParameter AddParameter(System.Data.IDbCommand command, string strParameterName, object objValue, System.Data.ParameterDirection pad, System.Data.DbType dbType)
{
System.Data.IDbDataParameter parameter = command.CreateParameter();
if (!strParameterName.StartsWith("@"))
{
strParameterName = "@" + strParameterName;
} // End if (!strParameterName.StartsWith("@"))
parameter.ParameterName = strParameterName;
parameter.DbType = dbType;
parameter.Direction = pad;
// Es ist keine Zuordnung von DbType UInt64 zu einem bekannten SqlDbType vorhanden.
// No association DbType UInt64 to a known SqlDbType
if (command.Parameters.Contains(strParameterName))
{
command.Parameters.RemoveAt(strParameterName);
}
SetParameter(parameter, objValue);
command.Parameters.Add(parameter);
return(parameter);
} // End Function AddParameter
/// <summary>
/// 判断数据库中是否存在指定的对象
/// </summary>
/// <param name="obj">对象</param>
/// <returns>true:数据库中存在指定关键字的记录 false:数据库中不存在指定关键字的记录</returns>
public bool Contains(object obj)
{
if (obj == null)
{
throw new ArgumentNullException("obj");
}
this.CheckBindInfo(obj.GetType(), true);
this.CheckConnetion();
TableBindInfo table = this.GetBindInfo(obj.GetType());
System.Collections.ArrayList values = new System.Collections.ArrayList();
string strSQL = this.BuildCondition(obj, values);
bool result = false;
if (strSQL != null)
{
using (System.Data.IDbCommand cmd = myConnection.CreateCommand())
{
strSQL = "Select 1 from " + FixTableName(table.TableName) + " Where " + strSQL;
cmd.CommandText = strSQL;
foreach (object v in values)
{
System.Data.IDbDataParameter p = cmd.CreateParameter();
p.Value = v;
cmd.Parameters.Add(p);
}
object v2 = cmd.ExecuteScalar();
if (v2 != null && DBNull.Value.Equals(v2) == false)
{
result = (Convert.ToInt32(v2) == 1);
}
}
}
return(false);
}
/// <summary>
/// 删除若干条对象的数据
/// </summary>
/// <param name="Objects">对象列表</param>
/// <returns>删除的记录个数</returns>
public int DeleteObjects(System.Collections.IEnumerable Objects)
{
if (Objects == null)
{
throw new ArgumentNullException("Objects");
}
this.CheckBindInfo(Objects, true);
this.CheckConnetion();
int RecordCount = 0;
using (System.Data.IDbCommand cmd = myConnection.CreateCommand())
{
foreach (object obj in Objects)
{
TableBindInfo table = this.GetBindInfo(obj.GetType());
// 拼凑SQL语句
System.Collections.ArrayList values = new System.Collections.ArrayList();
string strSQL = BuildCondition(obj, values);
strSQL = "Delete From " + FixTableName(table.TableName) + " Where " + strSQL;
// 设置SQL命令对象
cmd.Parameters.Clear();
cmd.CommandText = strSQL;
foreach (object v in values)
{
System.Data.IDbDataParameter p = cmd.CreateParameter();
p.Value = v;
cmd.Parameters.Add(p);
}
// 执行SQL，删除记录
RecordCount += cmd.ExecuteNonQuery();
}
}
return(RecordCount);
}
public virtual object ExecuteStoredProcedure(System.Data.IDbCommand cmd)
{
object objReturnValue = null;
try
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//// System.Data.SqlClient.SqlParameter returnValue = new System.Data.SqlClient.SqlParameter();
System.Data.IDbDataParameter returnValue = cmd.CreateParameter();
returnValue.Direction = System.Data.ParameterDirection.ReturnValue;
//this.AddParameter(cmd, "name", null, System.Data.ParameterDirection.ReturnValue);
cmd.Parameters.Add(returnValue);
using (System.Data.IDbConnection con = this.Connection)
{
cmd.Connection = con;
try
{
if (cmd.Connection.State != System.Data.ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
//Assert.AreEqual(123, (int)returnValue.Value);
//System.Diagnostics.Debug.Assert(123 == (int)((System.Data.IDataParameter)cmd.Parameters["name"]).Value);
//System.Diagnostics.Debug.Assert(123 == (int)returnValue.Value);
objReturnValue = returnValue.Value;
}
catch (System.Exception ex)
{
if (Log("cDAL.ExecuteStoredProcedure - inner", ex, cmd))
{
throw;
}
}
finally
{
if (cmd.Connection != null && cmd.Connection.State != System.Data.ConnectionState.Closed)
{
cmd.Connection.Close();
}
} // End Finally
} // End using con
}
catch (System.Exception ex)
{
if (Log("cDAL.ExecuteStoredProcedure - outer", ex, cmd))
{
throw;
}
} // End catch
return(objReturnValue);
} // End Function ExecuteStoredProcedure
// Study implementation of skip/take.
public List <Movie> GetMovies(int skip, int take)
{
List <Movie> movie = new List <Movie>();
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
// Demonstrate a text command.
// Be leary - but valid.
// Safe with int only.
// string text = $"select * from movies";
// command.CommandText = text;
// command.CommandType = System.Data.CommandType.Text;
command.CommandText = "[stored proc]";
command.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.IDataParameter P_skip = command.CreateParameter();
System.Data.IDataParameter P_take = command.CreateParameter();
P_skip.Direction = System.Data.ParameterDirection.Input;
P_skip.ParameterName = "@skip";
P_skip.DbType = System.Data.DbType.Int32;
P_skip.Value = skip;
P_take.Direction = System.Data.ParameterDirection.Input;
P_take.ParameterName = "@take";
P_take.DbType = System.Data.DbType.Int32;
P_take.Value = take;
command.Parameters.Add(P_skip);
command.Parameters.Add(P_take);
// Study the implementation.
using (System.Data.IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
movie.Add(MapMovie(reader));
}
}
}
return(movie);
}
public static System.Data.IDbCommand AddParameter(this System.Data.IDbCommand cmd, string name, object value)
{
var p = cmd.CreateParameter();
p.ParameterName = name;
p.Value = value;
cmd.Parameters.Add(p);
return(cmd);
}
public static void AddParameter <T>(this System.Data.IDbCommand self, T value, string name = null)
{
var p = self.CreateParameter();
p.Value = value;
if (!string.IsNullOrEmpty(name))
{
p.ParameterName = name;
}
self.Parameters.Add(p);
}
public Connection(System.Data.IDbConnection connection)
{
m_connection = connection;
m_errorcmd = m_connection.CreateCommand();
m_errorcmd.CommandText = @"INSERT INTO ""ErrorLog"" (""BackupID"", ""Message"", ""Exception"", ""Timestamp"") VALUES (?,?,?,?)";
for (var i = 0; i < 4; i++)
{
m_errorcmd.Parameters.Add(m_errorcmd.CreateParameter());
}
this.ApplicationSettings = new ApplicationSettings(this);
}
public static List <Dictionary <string, object> > DumpTable(System.Data.IDbCommand cmd, string tablename, string pagingfield, string offset_str, string pagesize_str)
{
var result = new List <Dictionary <string, object> >();
long pagesize;
if (!long.TryParse(pagesize_str, out pagesize))
{
pagesize = 100;
}
pagesize = Math.Max(10, Math.Min(500, pagesize));
cmd.CommandText = "SELECT * FROM \"" + tablename + "\"";
long offset = 0;
if (!string.IsNullOrWhiteSpace(offset_str) && long.TryParse(offset_str, out offset) && !string.IsNullOrEmpty(pagingfield))
{
var p = cmd.CreateParameter();
p.Value = offset;
cmd.Parameters.Add(p);
cmd.CommandText += " WHERE \"" + pagingfield + "\" < ?";
}
if (!string.IsNullOrEmpty(pagingfield))
{
cmd.CommandText += " ORDER BY \"" + pagingfield + "\" DESC";
}
cmd.CommandText += " LIMIT " + pagesize.ToString();
using (var rd = cmd.ExecuteReader())
{
var names = new List <string>();
for (var i = 0; i < rd.FieldCount; i++)
{
names.Add(rd.GetName(i));
}
while (rd.Read())
{
var dict = new Dictionary <string, object>();
for (int i = 0; i < names.Count; i++)
{
dict[names[i]] = rd.GetValue(i);
}
result.Add(dict);
}
}
return(result);
}
/// <summary>
/// 更新多个对象
/// </summary>
/// <param name="Objects">对象列表</param>
/// <returns>更新修改的数据库记录个数</returns>
public int UpdateObjects(System.Collections.IEnumerable Objects)
{
if (Objects == null)
{
throw new ArgumentNullException("Objects");
}
this.CheckBindInfo(Objects, true);
this.CheckConnetion();
int RecordCount = 0;
using (System.Data.IDbCommand cmd = myConnection.CreateCommand())
{
foreach (object obj in Objects)
{
TableBindInfo table = this.GetBindInfo(obj.GetType());
// 拼凑生成SQL更新语句
System.Collections.ArrayList values = new System.Collections.ArrayList();
System.Text.StringBuilder myStr = new System.Text.StringBuilder();
foreach (FieldBindInfo field in table.Fields)
{
object v = field.Property.GetValue(obj, null);
if (myStr.Length > 0)
{
myStr.Append(" , " + System.Environment.NewLine);
}
myStr.Append(FixFieldName(field.FieldName) + " = ? ");
values.Add(field.ToDataBase(v));
}
myStr.Insert(0, "Update " + FixTableName(table.TableName) + " Set ");
string strSQL = BuildCondition(obj, values);
myStr.Append(" Where " + strSQL);
strSQL = myStr.ToString();
// 设置SQL命令对象，填充参数
cmd.Parameters.Clear();
cmd.CommandText = strSQL;
foreach (object v in values)
{
System.Data.IDbDataParameter p = cmd.CreateParameter();
cmd.Parameters.Add(p);
p.Value = v;
}
RecordCount += cmd.ExecuteNonQuery();
} //foreach
} //using
return(RecordCount);
}
} // End Function GetDbType
//public static System.Data.IDbDataParameter AddWithValue(this System.Data.IDbCommand cmd, string parameterName, object value)
//{
// return AddWithValue((System.Data.Common.DbCommand)cmd, parameterName, value, null);
//}
public static System.Data.IDbDataParameter AddWithValue(this System.Data.IDbCommand cmd, string parameterName, object value, System.Data.DbType?dbType = null)
{
if (value == null)
{
value = System.DBNull.Value;
} // End if (objValue == null)
if (!dbType.HasValue)
{
dbType = GetDbType(value.GetType());
}
System.Data.IDbDataParameter p = cmd.CreateParameter();
p.ParameterName = parameterName;
p.DbType = dbType.Value;
p.Value = value;
cmd.Parameters.Add(p);
return(p);
}
public static void AddParameter(this System.Data.IDbCommand self)
{
self.Parameters.Add(self.CreateParameter());
}
/// <summary>
/// Execute a Call to the database according to configuration
/// </summary>
/// <param name="Service">Database Service Information</param>
/// <param name="Delegate">Database Callback</param>
/// <param name="ConnectionTimeout">Maximum TimeOut before Throw TimeoutException</param>
/// <returns>Return the Database Result</returns>
private object Execute(DataService Service, Func <System.Data.IDbConnection, System.Data.IDbCommand, object> Delegate, Int32 ConnectionTimeout)
{
//Initialize the Component's will be Connect to the Database
System.Data.IDbConnection DbConnection = new Tconnection();
DbConnection.ConnectionString = _connectionString;
System.Data.IDbCommand DbCommand = DbConnection.CreateCommand();
if (Service != null)
{
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
DbCommand.CommandText = Service.Command;
DbCommand.CommandTimeout = ConnectionTimeout;
}
//Fill Parameters
if (Service != null && Service.HasParameters())
{
Service.Parameters.ForEach((Parameter) =>
{
string parameterName = Parameter.Name;
System.Data.IDbDataParameter dbParameter = DbCommand.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Value = Parameter.Value;
dbParameter.Direction = Parameter.Direction;
//Special Threatment by Type
if (Parameter.Value != null)
{
ParameterParse(ref dbParameter, Parameter);
}
DbCommand.Parameters.Add(dbParameter);
});
}
try
{
//Open The Connection
if (DbConnection.State != System.Data.ConnectionState.Open)
{
DbConnection.Open();
}
//Call Handler
object ret = Delegate(DbConnection, DbCommand);
return(ret);
}
catch (System.Exception ex)
{
this.OnException(ex); //Overridable method!
//Throw Exception Between The Top Layers
throw new System.Exception(String.Format("[{0}] Ex: {1}", Service.Command, ex.Message), ex);
}
finally
{
//Finally Close The Connection [Always]
DbConnection.Close();
}
}
/// <summary>
/// Execute a Massive Call's to the database according to services configuration
/// </summary>
/// <param name="Service">Database Service Information</param>
/// <param name="Delegate">Database Callback</param>
/// <param name="ConnectionTimeout">Maximum TimeOut before Throw TimeoutException</param>
/// <returns></returns>
private void Execute(DataService[] Services, Action <System.Data.IDbCommand> Delegate, Int32 ConnectionTimeout)
{
//Initialize the Component's will be Connect to the Database
System.Data.IDbConnection DbConnection = new Tconnection();
DbConnection.ConnectionString = _connectionString;
DbConnection.Open();
System.Data.IDbTransaction _tran = DbConnection.BeginTransaction();
System.Data.IDbCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandTimeout = ConnectionTimeout;
DbCommand.Transaction = _tran;
string _currentService = "";
try
{
foreach (DataService Service in Services)
{
DbCommand.Parameters.Clear();
if (Service != null)
{
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
DbCommand.CommandText = _currentService = Service.Command;
}
//Fill Parameters
if (Service != null && Service.HasParameters())
{
Service.Parameters.ForEach((Parameter) =>
{
System.Data.IDbDataParameter dbParameter = DbCommand.CreateParameter();
dbParameter.ParameterName = Parameter.Name;
dbParameter.Value = Parameter.Value;
dbParameter.Direction = Parameter.Direction;
//Special Threatment by Type
if (Parameter.Value != null)
{
ParameterParse(ref dbParameter, Parameter);
}
DbCommand.Parameters.Add(dbParameter);
});
}
//Call Handler
Delegate(DbCommand);
}
//Call Handler
_tran.Commit();
}
catch (System.Exception ex)
{
//Throw Exception Between The Top Layers
_tran.Rollback();
DbConnection.Close();
throw new Gale.Exception.GaleException("ServiceTransactionError", _currentService, ex.Message);
}
finally
{
//Finally Close The Connection [Always]
DbConnection.Close();
}
}
/// <summary>
/// Cria o parametro para o comando.
/// </summary>
/// <returns></returns>
public System.Data.IDbDataParameter CreateParameter()
{
return(_command.CreateParameter());
}
} // End Sub SaveAssembly2
// http://stackoverflow.com/questions/2885335/clr-sql-assembly-get-the-bytestream
// http://stackoverflow.com/questions/891617/how-to-read-a-image-by-idatareader
// http://stackoverflow.com/questions/4103406/extracting-a-net-assembly-from-sql-server-2005
public virtual void SaveAssembly3(string assemblyName, string path)
{
string sql = @"
--DECLARE @__assemblyname nvarchar(260)
--SET @__assemblyname = 'Microsoft.SqlServer.Types'
SELECT
A.name
,AF.content
FROM sys.assembly_files AS AF
INNER JOIN sys.assemblies AS A
ON AF.assembly_id = A.assembly_id
WHERE AF.file_id = 1
AND A.name = @__assemblyname
;
";
using (System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection("context connection=true")) //Create current context connection
{
using (System.Data.IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
System.Data.IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = "@__assemblyname";
param.DbType = System.Data.DbType.String;
param.Value = assemblyName;
param.Size = 128;
cmd.Parameters.Add(param);
cmd.Prepare();
using (System.Data.IDataReader reader = cmd.ExecuteReader())
{
reader.Read();
const int BUFFER_SIZE = 1024;
byte[] buffer = new byte[BUFFER_SIZE];
int col = reader.GetOrdinal("content");
int bytesRead = 0;
int offset = 0;
// write the byte stream out to disk
using (System.IO.FileStream bytestream = new System.IO.FileStream(path, System.IO.FileMode.CreateNew))
{
while ((bytesRead = (int)reader.GetBytes(col, offset, buffer, 0, BUFFER_SIZE)) > 0)
{
bytestream.Write(buffer, 0, bytesRead);
offset += bytesRead;
} // Whend
bytestream.Close();
} // End Using bytestream
reader.Close();
} // End Using reader
} // End Using cmd
} // End Using conn
} // End Function SaveAssembly3
/// <summary>
/// 将若干个对象插入到数据库中
/// </summary>
/// <param name="Objects">对象列表</param>
/// <param name="TableName">制定的数据表，若未指定则使用默认的数据表名</param>
/// <returns>插入的数据库记录的个数</returns>
public int InsertObjects(System.Collections.IEnumerable Objects, string TableName)
{
if (Objects == null)
{
throw new ArgumentNullException("Objects");
}
this.CheckBindInfo(Objects, false);
System.Collections.ArrayList list = new System.Collections.ArrayList();
foreach (object obj in Objects)
{
list.Add(obj);
}
if (list.Count == 0)
{
return(0);
}
this.CheckConnetion();
// 上一次执行的SQL语句
string strLastSQL = null;
int InsertCount = 0;
using (System.Data.IDbCommand cmd = myConnection.CreateCommand())
{
foreach (object obj in list)
{
TableBindInfo table = this.GetBindInfo(obj.GetType());
string TableName2 = TableName;
if (TableName2 == null || TableName.Trim().Length == 0)
{
TableName2 = table.TableName;
}
System.Collections.ArrayList values = new System.Collections.ArrayList();
// 拼凑SQL语句
System.Text.StringBuilder myStr = new System.Text.StringBuilder();
System.Text.StringBuilder myFields = new System.Text.StringBuilder();
foreach (FieldBindInfo field in table.Fields)
{
if (field.Property.CanRead == false)
{
throw new Exception("属性 " + field.Property.Name + " 是不可写的");
}
object v = field.Property.GetValue(obj, null);
if (v == null || DBNull.Value.Equals(v))
{
continue;
}
values.Add(field.ToDataBase(v));
if (myStr.Length > 0)
{
myStr.Append(" , ");
myFields.Append(" , ");
}
myStr.Append(" ? ");
myFields.Append(FixFieldName(field.FieldName));
} //foreach
myStr.Insert(0, "Insert Into " + FixTableName(TableName2)
+ " ( " + myFields.ToString() + " ) Values ( ");
myStr.Append(" ) ");
string strSQL = myStr.ToString();
if (strSQL != strLastSQL)
{
// 重新设置SQL命令对象
strLastSQL = strSQL;
cmd.Parameters.Clear();
cmd.CommandText = strSQL;
for (int iCount = 0; iCount < values.Count; iCount++)
{
cmd.Parameters.Add(cmd.CreateParameter());
}
}
// 填充SQL命令参数值
for (int iCount = 0; iCount < values.Count; iCount++)
{
((System.Data.IDbDataParameter)cmd.Parameters[iCount]).Value = values[iCount];
}
// 执行SQL命令向数据表新增记录
InsertCount += cmd.ExecuteNonQuery();
} //foreach
} //using
return(InsertCount);
}
} // End Sub InitLargeFileSave
// http://stackoverflow.com/questions/9200675/blob-files-in-sql-database-as-chunk
public static void SqlServerLargeFileChuncked_tooSlow(string fileName)
{
string sql = "UPDATE _____save SET Data.Write(@data, LEN(data), @length) WHERE fileName = @fileName";
byte[] buffer = new byte[1024 * 1024 * 10];
string constr = SQL.GetConnectionString();
using (System.Data.IDbConnection con = SQL.GetConnection())
{
using (System.Data.IDbCommand cmd = SQL.CreateCommand(sql))
{
cmd.CommandText = sql;
//cmd.Parameters.Add("@data", System.Data.SqlDbType.VarBinary);
System.Data.IDbDataParameter dataParam = cmd.CreateParameter();
dataParam.ParameterName = "@data";
dataParam.DbType = System.Data.DbType.Binary;
System.Data.IDbDataParameter lengthParam = cmd.CreateParameter();
//cmd.Parameters.Add("@length", System.Data.SqlDbType.Int);
dataParam.ParameterName = "@length";
dataParam.DbType = System.Data.DbType.Int32;
System.Data.IDbDataParameter fileNameParam = cmd.CreateParameter();
//cmd.Parameters.Add("@fileName", System.Data.SqlDbType.NVarChar);
dataParam.ParameterName = "@length";
dataParam.DbType = System.Data.DbType.Int32;
fileNameParam.Value = fileName;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.IO.Stream fs = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
int readBytes = 0;
long fileSize = fs.Length;
long cIndex = 0;
while (cIndex < fileSize)
{
if (cIndex + buffer.Length > fileSize)
{
readBytes = (int)(fileSize - cIndex);
}
else
{
readBytes = buffer.Length;
}
fs.Read(buffer, 0, readBytes);
dataParam.Value = buffer;
dataParam.Size = readBytes;
lengthParam.Value = readBytes;
cmd.ExecuteNonQuery();
cIndex += buffer.Length;
} // Whend
} // End Using fs
} // End Using cmd
} // End Using con
} // End Sub SqlServerLargeFileChuncked
