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