internal static DbCommand CreateCommand(DbProviderFactory factory, Action<DbCommand> setup) { DbCommand command = factory.CreateCommand(); setup(command); return command; }
private static SDC.DbCommand BuildCommand(string sproc, List <ParamStruct> list) { SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider); SDC.DbCommand comm = factory.CreateCommand(); comm.CommandText = sproc; comm.CommandType = System.Data.CommandType.StoredProcedure; SDC.DbParameter param = factory.CreateParameter(); foreach (ParamStruct item in list) { param.ParameterName = item.paramId; param.Value = item.paramValue; param.DbType = item.paramType; param.Size = item.paramLength; param.Direction = item.paramDirection; comm.Parameters.Add(param); param = factory.CreateParameter(); } return(comm); }
protected void Initialize (string databaseName, string querySelectRowString) { string providerName = GetProviderNameByDBName(databaseName); string connectionString = GetConnectionStringByDBName(databaseName); // Create the DbProviderFactory and DbConnection. factory = DbProviderFactories.GetFactory(providerName); connection = factory.CreateConnection(); connection.ConnectionString = connectionString; // Create the DbCommand. DbCommand SelectTableCommand = factory.CreateCommand(); SelectTableCommand.CommandText = querySelectRowString; SelectTableCommand.Connection = connection; adapter = factory.CreateDataAdapter(); adapter.SelectCommand = SelectTableCommand; // Create the DbCommandBuilder. builder = factory.CreateCommandBuilder(); builder.DataAdapter = adapter; adapter.ContinueUpdateOnError = true; }
private static object CreateObject(DbProviderFactory factory, ProviderSupportedClasses kindOfObject, string providerName) { switch (kindOfObject) { case ProviderSupportedClasses.DbConnection: return factory.CreateConnection(); case ProviderSupportedClasses.DbDataAdapter: return factory.CreateDataAdapter(); case ProviderSupportedClasses.DbParameter: return factory.CreateParameter(); case ProviderSupportedClasses.DbCommand: return factory.CreateCommand(); case ProviderSupportedClasses.DbCommandBuilder: return factory.CreateCommandBuilder(); case ProviderSupportedClasses.DbDataSourceEnumerator: return factory.CreateDataSourceEnumerator(); case ProviderSupportedClasses.CodeAccessPermission: return factory.CreatePermission(PermissionState.None); } throw new InternalException(string.Format(CultureInfo.CurrentCulture, "Cannot create object of provider class identified by enum {0} for provider {1}", new object[] { Enum.GetName(typeof(ProviderSupportedClasses), kindOfObject), providerName })); }
// SchemaReader.ReadSchema public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory) { var result = new Tables(); var cmd = factory.CreateCommand(); cmd.Connection = connection; cmd.CommandText = TABLE_SQL; //pull the tables in a reader using (cmd) { using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Table tbl = new Table(); tbl.Name = rdr["TABLE_NAME"].ToString(); tbl.Schema = rdr["TABLE_SCHEMA"].ToString(); tbl.IsView = String.Compare(rdr["TABLE_TYPE"].ToString(), "View", true) == 0; tbl.CleanName = CleanUp(tbl.Name); tbl.ClassName = Inflector.MakeSingular(tbl.CleanName); result.Add(tbl); } } } //this will return everything for the DB var schema = connection.GetSchema("COLUMNS"); //loop again - but this time pull by table name foreach (var item in result) { item.Columns = new List<Column>(); //pull the columns from the schema var columns = schema.Select("TABLE_NAME='" + item.Name + "'"); foreach (var row in columns) { var type = GetPropertyType(row); Column col = new Column(); col.Name = row["COLUMN_NAME"].ToString(); col.PropertyName = CleanUp(col.Name); col.PropertyType = type; col.CustomType = type == null ? row["DATA_TYPE"].ToString().ToLowerInvariant() : null; col.Size = GetDatatypeSize(row["DATA_TYPE"].ToString()); col.Precision = GetDatatypePrecision(row["DATA_TYPE"].ToString()); col.IsNullable = row["IS_NULLABLE"].ToString() == "YES"; col.IsPrimaryKey = row["COLUMN_KEY"].ToString() == "PRI"; col.IsAutoIncrement = row["extra"].ToString().ToLower().IndexOf("auto_increment") >= 0; item.Columns.Add(col); } } return result; }
public int CreateDatabase(String queryString) { string lib = GetProviderFactoryLib(providerName); System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName); DbConnection con = provider.CreateConnection(); con.ConnectionString = BuildConnectionString(providerName, ""); int rowsAffected = 0; try { con.Open(); DbCommand com = provider.CreateCommand(); com.CommandTimeout = 30; com.Connection = con; com.CommandText = queryString; com.CommandType = CommandType.Text; object result = com.ExecuteNonQuery(); if (result != null) { rowsAffected = Convert.ToInt32(result); } } catch (Exception ex) { throw ex; } con.Close(); return(rowsAffected); }
private DbCommand BuildInsertCommand(System.Data.Common.DbProviderFactory provider, DbConnection con) { DbCommand com = provider.CreateCommand(); com.CommandType = CommandType.Text; com.Connection = con; string fieldList = SetFieldList(); if (providerName == ProviderFactoryType.MySql.ToString()) { com.CommandText = String.Format("Insert Into {0} SET {1}", this.tableName, fieldList) + ";" + this.LastInsertIdCommand(providerName); foreach (DataRow row in this.tableStruct.Rows) { MySqlParameter parameter = new MySqlParameter(); parameter.ParameterName = "?" + row["fieldName"]; parameter.MySqlDbType = GetMySqlType(row["ValueType"].ToString()); parameter.SourceColumn = row["fieldName"].ToString(); parameter.IsNullable = true; if (row["fieldName"].ToString() == this.identityColumn) { parameter.Direction = ParameterDirection.Output; } else { parameter.Direction = ParameterDirection.Input; } com.Parameters.Add(parameter); } } return(com); }
public static bool CheckMembershipSchemaVersion (DbProviderFactory factory, string connStr, string feature, string compatibleVersion) { using (DbConnection connection = CreateConnection (factory, connStr)) { DbCommand command = factory.CreateCommand (); command.Connection = connection; command.CommandText = @"aspnet_CheckSchemaVersion"; command.CommandType = CommandType.StoredProcedure; AddParameter (factory, command, "@Feature", ParameterDirection.Input, feature); AddParameter (factory, command, "@CompatibleSchemaVersion", ParameterDirection.Input, compatibleVersion); DbParameter returnValue = AddParameter (factory, command, "@ReturnVal", ParameterDirection.ReturnValue, null); try { command.ExecuteNonQuery (); } catch (Exception) { throw new ProviderException ("ASP.NET Membership schema not installed."); } if ((int) (returnValue.Value ?? -1) == 0) return true; return false; } }
private int LastInsertedId() { string lib = GetProviderFactoryLib(providerName); System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName); DbConnection con = provider.CreateConnection(); con.ConnectionString = BuildConnectionString(providerName, this.dbName); DbCommand com = provider.CreateCommand(); com.CommandTimeout = 30; com.Connection = con; com.CommandText = LastInsertIdCommand(providerName); com.CommandType = CommandType.Text; int lastId = 0; try { con.Open(); com.Prepare(); lastId = Convert.ToInt32(com.ExecuteScalar()); } catch (Exception ex) { throw ex; } con.Close(); return(lastId); }
public Database(string connectionString, string providerName) { _factory = DbProviderFactories.GetFactory(providerName); _com = _factory.CreateCommand(); _con = _factory.CreateConnection(); _con.ConnectionString = connectionString; _com.Connection = _con; }
public DataTable RetrieveTableStructure(string tableName) { DataTable tableStruct = new DataTable(); string lib = GetProviderFactoryLib(providerName); System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName); DbConnection con = provider.CreateConnection(); con.ConnectionString = BuildConnectionString(providerName, ""); DbCommand com = provider.CreateCommand(); com.CommandTimeout = 30; com.Connection = con; string queryString = ""; if (providerName == ProviderFactoryType.MySql.ToString()) { queryString = string.Format(@" SELECT lower( Column_name) as FieldName, Column_name as FieldCaption, Data_type As ValueType, 0 As ColumnIsHide, 0 As FieldIsHide, 0 As IsFake, 0 As IsReadOnly, 0 As IsRequiered, '' As FieldCategory, '' As UserHelp, Extra As Extra, if( isnull( Character_Maximum_Length),Numeric_Precision, Character_Maximum_Length) As Length, if( isnull(Numeric_Scale), 000, Numeric_Scale) As DecimalPosition From information_schema.COLUMNS Where Table_Schema = '{0}' And LOWER(Table_Name) = '{1}'" , this.dbName, tableName.ToLower()).Replace("\r", "").Replace("\n", " ").Replace("\t", " ").Replace(" ", " "); } com.CommandText = queryString; com.CommandType = CommandType.Text; DataSet ds = new DataSet(); DbDataAdapter da = provider.CreateDataAdapter(); da.SelectCommand = com; try { con.Open(); da.Fill(ds); tableStruct = ds.Tables[0]; } catch (Exception ex) { throw ex; } con.Close(); return(tableStruct); }
public DBHelper() { strConnectionString = ConfigurationManager.ConnectionStrings["Connectionstringname"].ConnectionString; objFactory = SqlClientFactory.Instance; objConnection = objFactory.CreateConnection(); objCommand = objFactory.CreateCommand(); objConnection.ConnectionString = strConnectionString; objCommand.Connection = objConnection; }
public static DbCommand CreateCommand(DbProviderFactory dbFactory, string commandText, DbConnection connection) { if (dbFactory == null) throw new ArgumentNullException("dbFactory"); var command = dbFactory.CreateCommand(); command.CommandText = commandText; command.Connection = connection; return command; }
///// <summary> ///// ///// </summary> //public SQLiteHelper() // : this(s_DefaultConnectionString) //{ //} /// <summary> /// /// </summary> /// <param name="provider"></param> /// <param name="connectionString"></param> public SQLiteHelper(string connectionString) { objFactory = System.Data.SQLite.SQLiteFactory.Instance; objConnection = objFactory.CreateConnection(); objCommand = new DbCommandWrapper(objFactory.CreateCommand()); objConnection.ConnectionString = connectionString; objCommand.Connection = objConnection; }
/// <summary> /// /// </summary> public DBTransactionManager(string _connectionKey) { //Cria uma conexao na base padrão do AppSettings (Web.config) dbConnectionProvider = new DBConnectionProvider(); dbConnection = dbConnectionProvider.GetConnection(_connectionKey); //Cria o objeto command a partir do tipo do Provider do AppSettings (Web.config) factory = DBConnectionProvider.GetDbFactory(); command = factory.CreateCommand(); }
protected DbCommand CreateCommand(DbExecuteParameter dbParameter, DbConnection dbConn, DbTransaction dbTrans = null) { if (IsSingleton) { if (dbCommand == null) { dbCommand = dbFactory.CreateCommand(); } } else { if (dbCommand != null) { dbCommand.Dispose(); } dbCommand = dbFactory.CreateCommand(); } if (dbTrans != null) { dbCommand.Transaction = dbTrans; } if (dbParameter.IsStoredProcedure) { dbCommand.CommandType = CommandType.StoredProcedure; } dbCommand.Connection = dbConn; dbCommand.CommandText = dbParameter.CommandText; dbCommand.CommandTimeout = dbParameter.ExectueTimeout; if (dbParameter.dbProviderParameters != null) { foreach (DbProviderParameter param in dbParameter.dbProviderParameters) { dbCommand.Parameters.Add(CreateParameter(param)); } } return(dbCommand); }
/// <summary> /// Basic DAL object to communicate with data source /// </summary> /// <param name="ConnectionString">Data source connection string</param> /// <param name="ProviderName">Data source provider name</param> public DataAccess(string ConnectionString, string ProviderName) { this.conStr = ConnectionString; this.prName = ProviderName; factory = DbProviderFactories.GetFactory(prName); cn = factory.CreateConnection(); com = factory.CreateCommand(); com.Connection = cn; cn.ConnectionString = conStr; }
public static SDC.DbDataReader ReadData(string sproc, List <ParamStruct> paramList) { SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider); SDC.DbCommand comm = factory.CreateCommand(); comm = BuildCommand(sproc, paramList); SDC.DbConnection conn = Connection(factory); comm.Connection = conn; conn.Open(); SDC.DbDataReader dr = comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return(dr); }
public ControladorDB(string dataNameConnectionInConfigFile) { _exception = null; _dataNameConnectionInConfigFile = dataNameConnectionInConfigFile; try { _dbFactory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[_dataNameConnectionInConfigFile].ProviderName); } catch (Exception ex) { _exception = new Exception("No fue posible obtener el proveedor de datos especificado en el archivo de configuración de la aplicación.", ex); return; } _command = _dbFactory.CreateCommand(); _command.CommandType = CommandType.StoredProcedure; _command.Connection = _dbFactory.CreateConnection(); }
public override TableCollection ReadSchema(DbConnection connection, DbProviderFactory factory) { var result = new TableCollection(); _connection = connection; _factory = factory; var cmd = _factory.CreateCommand(); cmd.Connection = connection; cmd.CommandText = TABLE_SQL; //pull the TableCollection in a reader using (cmd) { using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { var tbl = new Table(); tbl.Name = rdr["TABLE_NAME"].ToString(); tbl.Schema = rdr["TABLE_SCHEMA"].ToString(); tbl.IsView = String.Compare(rdr["TABLE_TYPE"].ToString(), "View", StringComparison.OrdinalIgnoreCase) == 0; tbl.CleanName = CleanUp(tbl.Name); tbl.ClassName = Inflector.Instance.MakeSingular(tbl.CleanName); result.Add(tbl); } } } foreach (var tbl in result) { tbl.Columns = LoadColumns(tbl); // Mark the primary key var primaryKey = GetPrimaryKey(tbl.Name); var pkColumn = tbl.Columns.SingleOrDefault(x => x.Name.ToLower().Trim() == primaryKey.ToLower().Trim()); if (pkColumn != null) { pkColumn.IsPrimaryKey = true; } } return result; }
public HospitalDAL(string dataProvider, string connectionString) { try { _dbProviderFactory = DbProviderFactories.GetFactory(dataProvider); _sqlConnection = _dbProviderFactory.CreateConnection(); _command = _dbProviderFactory.CreateCommand(); _command.Connection = _sqlConnection; _sqlConnection.ConnectionString = connectionString; } catch (Exception ex) { //LOGGING Logger.Error("Can't load data storage!",ex); throw new InvalidOperationException("Can't load data storage!"); } }
private DbCommand BuildSelectCommand(System.Data.Common.DbProviderFactory provider, DbConnection con, string queryString, params dynamic[] args) { DbCommand com = provider.CreateCommand(); com.CommandTimeout = 30; com.Connection = con; com.CommandText = queryString; com.CommandType = CommandType.Text; foreach (var param in args) { DbParameter par = provider.CreateParameter(); par.Direction = ParameterDirection.Input; par.ParameterName = param.Name; par.Value = param.Value; com.Parameters.Add(par); } return(com); }
private void AddCommandInitStatements(IList statements, CodeExpression commandExpression, DbSourceCommand command, DbProviderFactory currentFactory, bool isFunctionsDataComponent) { if (((statements == null) || (commandExpression == null)) || (command == null)) { throw new InternalException("Argument should not be null."); } Type parameterType = currentFactory.CreateParameter().GetType(); Type type = currentFactory.CreateCommand().GetType(); CodeExpression parameterVariable = null; statements.Add(CodeGenHelper.Assign(commandExpression, CodeGenHelper.New(CodeGenHelper.GlobalType(type), new CodeExpression[0]))); if (isFunctionsDataComponent) { commandExpression = CodeGenHelper.Cast(CodeGenHelper.GlobalType(type), commandExpression); } if ((((DbSource) command.Parent).Connection == null) || ((this.designTable.Connection != null) && (this.designTable.Connection == ((DbSource) command.Parent).Connection))) { statements.Add(CodeGenHelper.Assign(CodeGenHelper.Property(commandExpression, "Connection"), CodeGenHelper.Property(CodeGenHelper.This(), DataComponentNameHandler.DefaultConnectionPropertyName))); } else { Type type3 = currentFactory.CreateConnection().GetType(); IDesignConnection connection = ((DbSource) command.Parent).Connection; CodeExpression propertyReference = null; if (connection.PropertyReference == null) { propertyReference = CodeGenHelper.Str(connection.ConnectionStringObject.ToFullString()); } else { propertyReference = connection.PropertyReference; } statements.Add(CodeGenHelper.Assign(CodeGenHelper.Property(commandExpression, "Connection"), CodeGenHelper.New(CodeGenHelper.GlobalType(type3), new CodeExpression[] { propertyReference }))); } statements.Add(QueryGeneratorBase.SetCommandTextStatement(commandExpression, command.CommandText)); statements.Add(QueryGeneratorBase.SetCommandTypeStatement(commandExpression, command.CommandType)); if (command.Parameters != null) { foreach (DesignParameter parameter in command.Parameters) { parameterVariable = QueryGeneratorBase.AddNewParameterStatements(parameter, parameterType, currentFactory, statements, parameterVariable); statements.Add(CodeGenHelper.Stm(CodeGenHelper.MethodCall(CodeGenHelper.Property(commandExpression, "Parameters"), "Add", new CodeExpression[] { parameterVariable }))); } } }
private DbCommand BuildDeleteCommand(System.Data.Common.DbProviderFactory provider, DbConnection con) { DbCommand com = provider.CreateCommand(); com.CommandType = CommandType.Text; com.Connection = con; if (providerName == ProviderFactoryType.MySql.ToString()) { com.CommandText = String.Format("Delete From {0} WHERE {1} = ?{1}", this.tableName, this.identityColumn); MySqlParameter parameter = new MySqlParameter(); parameter.ParameterName = "?" + this.identityColumn; parameter.MySqlDbType = MySqlDbType.Int32; parameter.Direction = ParameterDirection.Input; parameter.SourceColumn = this.identityColumn; com.Parameters.Add(parameter); } return(com); }
public DbHelper(string connectionString, Providers provider) { strConnectionString = connectionString; switch (provider) { case Providers.SqlServer: objFactory = SqlClientFactory.Instance; break; case Providers.OleDb: objFactory = OleDbFactory.Instance; break; //case Providers.Oracle: // objFactory = OracleClientFactory.Instance; // break; case Providers.ODBC: objFactory = OdbcFactory.Instance; break; case Providers.ConfigDefined: string providername = ConfigurationManager.ConnectionStrings["PFDConnectionString"].ProviderName; switch (providername) { case "System.Data.SqlClient": objFactory = SqlClientFactory.Instance; break; case "System.Data.OleDb": objFactory = OleDbFactory.Instance; break; //case "System.Data.OracleClient": // objFactory = OracleClientFactory.Instance; // break; case "System.Data.Odbc": objFactory = OdbcFactory.Instance; break; } break; } objConnection = objFactory.CreateConnection(); objCommand = objFactory.CreateCommand(); objConnection.ConnectionString = strConnectionString; objCommand.Connection = objConnection; }
public static DataTable ReadTable(string sproc, List <ParamStruct> paramList) { DataTable dt = new DataTable(); SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider); SDC.DbCommand comm = factory.CreateCommand(); comm = BuildCommand(sproc, paramList); SDC.DbDataAdapter da = factory.CreateDataAdapter(); SDC.DbConnection conn = Connection(factory); comm.Connection = conn; da.SelectCommand = comm; da.MissingSchemaAction = MissingSchemaAction.AddWithKey; using (conn) { conn.Open(); da.Fill(dt); return(dt); } }
public DataTable ExecuteQuery(String queryString, params dynamic[] args) { readConectionSettings(); string lib = GetProviderFactoryLib(providerName); provider = RegisterProvider(providerName); con = provider.CreateConnection(); con.ConnectionString = BuildConnectionString(providerName, this.dbName); DataTable table = null; DbCommand com = provider.CreateCommand(); com.CommandTimeout = 30; com.Connection = con; com.CommandText = queryString; com.CommandType = CommandType.Text; dataSet = new DataSet(); dataAdapter = provider.CreateDataAdapter(); dataAdapter.SelectCommand = com; foreach (var param in args) { DbParameter par = provider.CreateParameter(); par.Direction = ParameterDirection.Input; par.ParameterName = param.Name; par.Value = param.Value; dataAdapter.SelectCommand.Parameters.Add(par); } try { con.Open(); dataAdapter.Fill(dataSet); table = dataSet.Tables[0]; } catch (Exception ex) { Console.Write(ex); throw ex; } con.Close(); return(table); }
public int ExecuteNonQuery(String queryString, bool silence, params dynamic[] args) { string lib = GetProviderFactoryLib(providerName); System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName); DbConnection con = provider.CreateConnection(); con.ConnectionString = BuildConnectionString(providerName, this.dbName); DbCommand com = provider.CreateCommand(); com.CommandTimeout = 30; com.Connection = con; com.CommandText = queryString; com.CommandType = CommandType.Text; foreach (var param in args) { DbParameter par = provider.CreateParameter(); par.Direction = ParameterDirection.Input; par.ParameterName = param.Name; par.Value = param.Value; com.Parameters.Add(par); } int rowsAffected = 0; try { con.Open(); object result = com.ExecuteNonQuery(); if (result != null) { rowsAffected = Convert.ToInt32(result); } } catch (Exception ex) { throw ex; } con.Close(); return(rowsAffected); }
public DataTable getDataQuery(String queryString, params dynamic[] args) { if (con == null) { LoadConnection(); } DataTable table = null; DbCommand com = provider.CreateCommand(); com.CommandTimeout = 30; com.Connection = this.con; com.CommandText = queryString; com.CommandType = CommandType.Text; dataSet = new DataSet(); dataAdapter = provider.CreateDataAdapter(); dataAdapter.SelectCommand = com; if (args != null && args.Length > 0) { foreach (var param in args) { DbParameter par = provider.CreateParameter(); par.Direction = ParameterDirection.Input; par.ParameterName = param.Name; par.Value = param.Value; dataAdapter.SelectCommand.Parameters.Add(par); } } try { con.Open(); dataAdapter.Fill(dataSet); table = dataSet.Tables[0]; } catch (Exception ex) { throw ex; } con.Close(); return(table); }
/// <summary> /// Extracts the command information from the command object and add specific information based on the factory being use. /// </summary> /// <param name="theCommand">Command to be processed.</param> /// <param name="factory">The factory to be use.</param> public static void DeriveParameters(DbCommand theCommand, DbProviderFactory factory) { theCommand.Parameters.Clear(); if (theCommand.CommandType == CommandType.StoredProcedure) { using (DbConnection conn = factory.CreateConnection()) { conn.ConnectionString = theCommand.Connection.ConnectionString; conn.Open(); using (DbCommand pivotCommand = factory.CreateCommand()) { pivotCommand.CommandText = theCommand.CommandText; pivotCommand.CommandType = theCommand.CommandType; pivotCommand.Connection = conn; if (theCommand is System.Data.OleDb.OleDbCommand) { if (conn.ConnectionString.Contains("Provider=Microsoft.Jet")) { DeriveParametersFromProcedureCode(conn, pivotCommand); } else System.Data.OleDb.OleDbCommandBuilder.DeriveParameters((System.Data.OleDb.OleDbCommand)pivotCommand); } else if (theCommand is System.Data.SqlClient.SqlCommand) { System.Data.SqlClient.SqlCommandBuilder.DeriveParameters((System.Data.SqlClient.SqlCommand)pivotCommand); } else if (theCommand is System.Data.OracleClient.OracleCommand) { System.Data.OracleClient.OracleCommandBuilder.DeriveParameters((System.Data.OracleClient.OracleCommand)pivotCommand); } foreach (DbParameter parameter in pivotCommand.Parameters) { System.Data.IDataParameter cloneParameter = (System.Data.IDataParameter)((ICloneable)parameter).Clone(); theCommand.Parameters.Add(cloneParameter); } } } } }
public Database(string connectionstring, Providers provider) { strConnectionString = connectionstring; switch (provider) { case Providers.SqlServer: objFactory = SqlClientFactory.Instance; break; case Providers.OleDb: objFactory = OleDbFactory.Instance; break; case Providers.ODBC: objFactory = OdbcFactory.Instance; break; case Providers.ConfigDefined: string providername = ConfigurationManager.ConnectionStrings["ResearchCenter"].ProviderName; switch (providername) { case "System.Data.SqlClient": objFactory = SqlClientFactory.Instance; break; case "System.Data.OleDb": objFactory = OleDbFactory.Instance; break; case "System.Data.Odbc": objFactory = OdbcFactory.Instance; break; default: objFactory = SqlClientFactory.Instance; break; } break; } objConnection = objFactory.CreateConnection(); objCommand = objFactory.CreateCommand(); objConnection.ConnectionString = strConnectionString; objCommand.Connection = objConnection; }
private DbCommand BuildUpdateCommand(System.Data.Common.DbProviderFactory provider, DbConnection con) { DbCommand com = provider.CreateCommand(); com.CommandType = CommandType.Text; com.Connection = con; string fieldList = SetFieldList(); if (providerName == ProviderFactoryType.MySql.ToString()) { com.CommandText = String.Format("Update {0} SET {2} WHERE {1} = ?{1}", this.tableName, this.identityColumn, fieldList); foreach (DataRow row in this.tableStruct.Rows) { MySqlParameter parameter = new MySqlParameter(); parameter.ParameterName = "?" + row["fieldName"]; parameter.MySqlDbType = GetMySqlType(row["ValueType"].ToString()); parameter.Direction = ParameterDirection.Input; parameter.SourceColumn = row["fieldCaption"].ToString(); com.Parameters.Add(parameter); } } return(com); }
public static int SendData(string sproc, List <ParamStruct> paramList) { SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider); SDC.DbCommand comm = factory.CreateCommand(); comm = BuildCommand(sproc, paramList); SDC.DbConnection conn = Connection(factory); comm.Connection = conn; conn.Open(); int count = comm.ExecuteNonQuery(); for (int i = 0; i < comm.Parameters.Count - 1; i++) { ParamStruct x = paramList[i]; x.paramValue = comm.Parameters[i].Value; paramList[i] = x; } return(count); }
public DbCommand CreateInsertCommand(string tableName, string[] columns, DbType[] types, object[] values) { Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!"); Check.Require(columns == null || (columns != null && types != null && values != null && columns.Length == types.Length && columns.Length == values.Length), "length of columns, types and values must equal!"); DbCommand cmd = fac.CreateCommand(); cmd.CommandType = CommandType.Text; StringBuilder sb = new StringBuilder("INSERT INTO "); sb.Append(leftToken); sb.Append(tableName.TrimStart(leftToken).TrimEnd(rightToken)); sb.Append(rightToken); sb.Append(' '); if (columns == null || columns.Length == 0) { sb.Append("DEFAULT VALUES"); } else { sb.Append('('); for (int i = 0; i < columns.Length; ++i) { if (columns[i].Trim()[0] == '[') { sb.Append(columns[i].Replace("[", leftToken.ToString()).Replace("]", rightToken.ToString())); } else { sb.Append(leftToken); sb.Append(columns[i].TrimStart(leftToken).TrimEnd(rightToken)); sb.Append(rightToken); } if (i < columns.Length - 1) { sb.Append(','); } } sb.Append(") VALUES ("); for (int i = 0; i < columns.Length; ++i) { if (values[i] != null && values[i] is ExpressionClip) { ExpressionClip expr = (ExpressionClip)values[i]; sb.Append(expr.ToString()); AddExpressionParameters(expr, cmd); if (i < columns.Length - 1) { sb.Append(','); } } else { string paramName = MakeUniqueParamNameWithPrefixToken(); sb.Append(paramName); if (i < columns.Length - 1) { sb.Append(','); } DbParameter p = cmd.CreateParameter(); p.ParameterName = paramName; p.DbType = types[i]; p.Value = values[i] == null ? DBNull.Value : values[i]; cmd.Parameters.Add(p); } } sb.Append(')'); } cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken); PrepareCommand(cmd); return(cmd); }
/// <summary> /// 创建命令 /// </summary> /// <returns></returns> public DbCommand CreateCommand() { return(dbFactory.CreateCommand()); }
private void InsertData() { try { DateTime dateValue = System.DateTime.Today; string dy = dateValue.ToString("dddddddd"); providerFactory = aspnetforum.Utils.DB.CreateDBProviderFactory(); cn = aspnetforum.Utils.DB.CreateConnection(); cmd = providerFactory.CreateCommand(); cmd.Connection = cn; this.cn.Open(); oCompanyInfo.StrUserName = AppLib.GetLoggedInUserName(); int sleep = Convert.ToInt32(ddlQualitySleep.SelectedValue); int stress = Convert.ToInt32(ddlLevel.SelectedValue); int outlook = Convert.ToInt32(ddloutlook.SelectedValue); int engagement = Convert.ToInt32(ddlengagement.SelectedValue); int energy = Convert.ToInt32(ddlenergylevel.SelectedValue); if (sleep == 0 || stress == 0 || outlook == 0 || engagement == 0 || energy == 0) lblMsg.Text = "You must answer each question to save."; else { string sUsername = Membership.GetUser().UserName; BLL.UserLib oUserLib = new BLL.UserLib(); Entity.UserInfo oUserInfo = new Entity.UserInfo(); SqlConnClass objSqlConnClass = new SqlConnClass(); DDClass objDDClass = new DDClass(objSqlConnClass.OpenConnection()); DataSet DS = objDDClass.AddupdateWellness(sUsername); if (DS.Tables[0].Rows.Count > 0) { DataTable dt = new DataTable(); DateTime datee = DateTime.Today; string[] dateArr = new string[DS.Tables[0].Rows.Count]; for (int i = 0; i < DS.Tables[0].Rows.Count; i++) { DateTime maxdate = Convert.ToDateTime(DS.Tables[0].Rows[i]["Dates"].ToString()); if (maxdate == datee) { this.cmd.CommandText = "Update tbl_wellnessEntry set UserName='******',Sleep='" + sleep + "',Stress='" + stress + "',Outlook='" + outlook + "',Engagement='" + engagement + "',Energy='" + energy + "',Day='" + dy + "',Dates='" + maxdate + "' where Dates='" + datee + "'"; this.cmd.ExecuteNonQuery(); this.cn.Close(); BindEvents(); bindChart(); // Page.Response.Redirect(Page.Request.Url.ToString(), true); //lblMsg.Text = "* Your wellness Diary is Updated Successfully"; } } } else { this.cmd.CommandText = "INSERT INTO tbl_wellnessEntry(UserName, Sleep, Stress, Outlook, Engagement, Energy, Day, Dates) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, sUsername, sleep, stress, outlook, engagement, energy, dy, System.DateTime.Today.ToShortDateString()); this.cmd.ExecuteNonQuery(); this.cn.Close(); BindEvents(); bindChart(); // Page.Response.Redirect(Page.Request.Url.ToString(), true); //lblMsg.Text = "* Your wellness Diary is Inserted Successfully"; } } } catch (Exception ex) { } }
/// <summary> /// Called when Validate returns DTSValidationStatus.VS_NEEDSNEWMETADATA. /// Also called by the GUI when the user changes the database connection and/or table-name. /// This function queries the schema of the external database table (the destination) /// and sets up the values for ComponentMetaData.ExternalMetadataColumnCollection. /// </summary> public override void ReinitializeMetaData() { base.ReinitializeMetaData(); // if we are in a connected state, continue. if (!m_isConnected) { bool bCancel; ErrorSupport.FireError( HResults.DTS_E_CONNECTIONREQUIREDFORMETADATA, out bCancel); throw new PipelineComponentHResultException( HResults.DTS_E_CONNECTIONREQUIREDFORMETADATA); } // get the input IDTSInput100 iDTSInput = ComponentMetaData.InputCollection[0]; // remove all input columns and external metadata columns iDTSInput.ExternalMetadataColumnCollection.RemoveAll(); iDTSInput.InputColumnCollection.RemoveAll(); m_DbFactory = GetDbFactory(); PostDiagnosticMessage( Localized.DiagnosticPre("DbProviderFactory.CreateCommand")); DbCommand selectCmd = m_DbFactory.CreateCommand(); PostDiagnosticMessage( Localized.DiagnosticPost("DbProviderFactory.CreateCommand Finished")); IDTSCustomProperty100 propTableName = ComponentMetaData.CustomPropertyCollection[TABLE_OR_VIEW_NAME]; m_fullTableName = (String.IsNullOrEmpty((string)propTableName.Value)) ? null : ((string)propTableName.Value).Trim(); selectCmd.CommandText = "select * from " + m_fullTableName; // do not localize selectCmd.Connection = m_DbConnection; selectCmd.CommandTimeout = m_commandTimeout; DataTable metadataTbl = null; try { metadataTbl = GetMetadataTableByCommand(selectCmd); } catch (Exception e) { // only post diagnostic message. // Error mesasge will only be posted to UI if the second attemp has also failed. PostDiagnosticMessage(Localized.FailureGetMetadataTableByCommand(e.Message)); // throw pipeline exception for failed to get schema table. bool bCancel; ErrorSupport.FireErrorWithArgs(HResults.DTS_E_ADODESTGETSCHEMATABLEFAILED, out bCancel, String.Format(CultureInfo.CurrentCulture, "\n{0}\n{1}", e.Message, e.Message)); throw new PipelineComponentHResultException(HResults.DTS_E_ADODESTGETSCHEMATABLEFAILED); } SetExternalMetadataInfos( iDTSInput.ExternalMetadataColumnCollection, metadataTbl); }
// SchemaReader.ReadSchema public Tables ReadSchema(DbConnection connection, DbProviderFactory factory) { var result = new Tables(); _connection=connection; _factory=factory; var cmd = _factory.CreateCommand(); cmd.Connection = connection; cmd.CommandText = ConstructTableSelectSql(); //pull the tables in a reader using(cmd) { using (var rdr = cmd.ExecuteReader()) { while(rdr.Read()) { Table tbl=new Table(); tbl.Name=rdr["TABLE_NAME"].ToString(); tbl.Schema=rdr["TABLE_SCHEMA"].ToString(); //tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0; tbl.CleanName=CleanUp(tbl.Name); tbl.ClassName=Inflector.MakeSingular(tbl.CleanName); if (tbl.Name.EndsWith("Enum")) { tbl.EnumName = tbl.ClassName; tbl.IsEnum = true; tbl.ClassName += "Poco"; } else { tbl.EnumName = null; tbl.IsEnum = false; } tbl.RepoName = tbl.ClassName + "Db"; result.Add(tbl); } } } foreach (var tbl in result) { tbl.Columns = LoadColumns(tbl); // Mark the primary key(s) string[] pkNames = GetPK(tbl.Name); if (pkNames != null) { var pkCols = tbl.Columns.Where(a => pkNames.Contains(a.Name)); foreach (var c in pkCols) { c.IsPK = true; } tbl.IsAutoIncrement = pkCols.Any(a => a.IsAutoIncrement); } } return result; }
/// <summary> /// Creates a connection and caches it so that it can be used later to submit SQL. /// </summary> /// /// <param name="factory" type = "DbProviderFactory">The factory to create the connection against.</param> /// <param name="connectionString" type = "string">The connection string used to make the connnection to the database.</param> /// <remarks> /// /// <RevisionHistory> /// Author Date Description /// DLGenerator 12/27/2014 6:55:52 PM Created function /// /// </RevisionHistory> /// /// </remarks> private void CacheConnection(DbProviderFactory factory, string connectionString) { try { // create a connection using the built-in factory and cache // the command object. _connectionString = connectionString; // set class level instance and cache connection string _newFactory = factory; _newConnection = factory.CreateConnection(); _newCommand = factory.CreateCommand(); _newConnection.ConnectionString = connectionString; _newCommand.Connection = _newConnection; } catch (Exception ex) { ProcessException(ex); } }
/// <summary> /// 打开数据库连接 /// </summary> public void Open() { if (conn == null) { Factory = DbFactory.CreateDbProviderFactory(DataType); comm = Factory.CreateCommand(); comm.CommandTimeout = _commandTimeout; conn = Factory.CreateConnection(); conn.ConnectionString = _connectionString; comm.Connection = conn; } if (conn.State == ConnectionState.Closed) { conn.Open(); comm.Parameters.Clear(); } }
/// <summary> /// 构造函数 /// </summary> public DBHelper(string providerName, string connectionString, string sqlType) { this._sqlType = sqlType; if (string.Compare(sqlType, "Mysql", true) == 0) { _connection = new MySqlConnection(); _command = new MySqlCommand(); this._paraPrefix = "?"; } else { _provider = GetDbProvider(providerName); _connection = _provider.CreateConnection(); _command = _provider.CreateCommand(); if (string.Compare(sqlType, "Oracle", true) == 0) { this._paraPrefix = ":"; } else if (string.Compare(sqlType, "SQLServer", true) == 0) { this._paraPrefix = "@"; } } _connection.ConnectionString = connectionString; _command.Connection = _connection; }
public static void ImportTemplateWithDataReader(DbProviderFactory factory, ImporterSettingsFactory settings) { using (var connection = factory.CreateConnection()) { Debug.Assert(connection != null, "connection != null"); connection.ConnectionString = settings.ConnectionString.ConnectionString; var command = factory.CreateCommand(); Debug.Assert(command != null, "command != null"); command.Connection = connection; command.CommandText = settings.QueryString; connection.Open(); using (var reader = command.ExecuteReader()) { List<StayData> stays = new List<StayData>(); int recordCount = 0; int recordErrors = 0; ResultLogger resultsLogger = new StringBuildResults(); while (reader.Read()) { recordCount++; try { var stay = new StayData { Room = reader.IsDBNull(0) ? string.Empty : reader.GetString(0), Reservation = reader.IsDBNull(1) ? string.Empty : reader.GetString(1), PeriodStart = reader.GetDateTime(2), PeriodEnd = reader.GetDateTime(3), IsCheckedIn = Convert.ToBoolean(reader.GetByte(4)), AllowExpressCheckout = Convert.ToBoolean(reader.GetByte(5)), CurrentBillValue = (reader.IsDBNull(6) ? (double?)null : reader.GetDouble(6)), CurrentBillDate = reader.IsDBNull(7) ? (DateTime?)null : reader.GetDateTime(7), CurrentBillCurrency = reader.IsDBNull(8) ? string.Empty : reader.GetString(8), IsBreakfastIncluded = Convert.ToBoolean(reader.GetByte(9)), GuestEmail = reader.IsDBNull(10) ? string.Empty : reader.GetString(10), GuestFirstName = reader.IsDBNull(11) ? string.Empty : reader.GetString(11), GuestLastName = reader.IsDBNull(12) ? string.Empty : reader.GetString(12), GuestPhoneNumber = reader.IsDBNull(13) ? string.Empty : reader.GetString(13), ExternalKey = reader.IsDBNull(14) ? string.Empty : reader.GetString(14), State = (StayStates)Enum.Parse(typeof(StayStates), reader.GetString(15)) }; stays.Add(stay); } catch (Exception e) { //Log.AppendFormat("Error read {0}(Message:{1})", reader.GetString(14), e.Message); //Log.AppendLine(); //SendToRaygun(e); System.Console.WriteLine(e.Message); recordErrors++; } if (1 == stays.Count) { //ImportStayBatch(settings, stays, resultsLogger); stays.Clear(); } } reader.Close(); if (0 < stays.Count) { //ImportStayBatch(settings, stays, resultsLogger); } Log.AppendLine(); Log.AppendFormat("Found {0} records to import.", recordCount); Log.AppendLine(); Log.AppendFormat("Found {0} records with errors.", recordErrors); Log.Append(resultsLogger); } } }
/// <summary> /// 打开数据库连接 /// </summary> public void Open() { if (conn == null) { Factory = DbProviderFactories.GetFactory(DataType.GetName()); comm = Factory.CreateCommand(); comm.CommandTimeout = CommandTimeout; conn = Factory.CreateConnection(); conn.ConnectionString = ConnectionString; comm.Connection = conn; } if (conn.State == ConnectionState.Closed) { conn.Open(); } }
/// <summary> /// 打开数据库连接 /// </summary> private void Open() { if (_conn == null) { _factory = DbFactory.CreateDbProviderFactory(DataType); _comm = _factory.CreateCommand(); _comm.CommandTimeout = _commandTimeout; _conn = _factory.CreateConnection(); _conn.ConnectionString = _connectionString; _comm.Connection = _conn; } if (_conn.State == ConnectionState.Closed) { _conn.Open(); _comm.Parameters.Clear(); } }
/// <summary> /// Determines the correct provider to use and sets up the connection and command /// objects for use in other methods /// </summary> /// <param name="connectString">The full connection string to the database</param> /// <param name="providerlist">The enum value of providers from dbutilities.Providers</param> public void CreateDBObjects(string connectString, Providers providerList) { //CreateDBObjects(connectString, providerList, null); switch (providerList) { case Providers.SqlServer: _factory = SqlClientFactory.Instance; break; case Providers.Oracle: _factory = OracleClientFactory.Instance; break; case Providers.OleDB: _factory = OleDbFactory.Instance; break; case Providers.ODBC: _factory = OdbcFactory.Instance; break; } _connection = _factory.CreateConnection(); _command = _factory.CreateCommand(); _connection.ConnectionString = connectString; _command.Connection = connection; }
public void SetSession(SqlProviders pType, string sHost, string sPort = "", string sUser = "", string sPass = "", string sData = "") { switch (pType) { case SqlProviders.SqlServer: dbFactory = SqlClientFactory.Instance; sConnection = "Server=" + sHost + (sPort.Length > 0 ? "," + sPort : ""); if (sUser != "") { sConnection += ";User Id=" + sUser; if (sPass != "") { sConnection += ";Password="******";Integrated Security=True;Trusted_Connection=True"; } sConnection += ";Initial Catalog=" + sData + ";"; dbConnection = dbFactory.CreateConnection(); dbCommand = dbFactory.CreateCommand(); dbConnection.ConnectionString = sConnection; dbCommand.Connection = dbConnection; break; case SqlProviders.SQLite: sqlFactory = SQLiteFactory.Instance; sDbPathSQLite = sHost + @"\" + sDbFileSQLite; if (!Directory.Exists(sHost)) { Directory.CreateDirectory(sHost); } SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder(); sb.DefaultTimeout = 5000; sb.SyncMode = SynchronizationModes.Off; sb.JournalMode = SQLiteJournalModeEnum.Memory; sb.PageSize = 65536; sb.CacheSize = 16777216; sb.FailIfMissing = false; sb.ReadOnly = false; sConnection = "data source=" + @sDbPathSQLite + ";" + sb.ConnectionString; sqlConnection = (SQLiteConnection)sqlFactory.CreateConnection(); sqlCommand = (SQLiteCommand)sqlFactory.CreateCommand(); sqlConnection.ConnectionString = sConnection; sqlCommand.Connection = sqlConnection; break; } }
public IDbCommand CreateCommand() { //return blank command return(_frameworkProviderFactory.CreateCommand()); }
private void SendContactUsEmail() { string strContent = string.Empty; BLL.TemplateLib oTemplateLib = new BLL.TemplateLib(); strContent = oTemplateLib.GetTemplateDetailsByTemplateTemplateName("HealthCoachSubmitQuestionEmail").TextAreaHTML; Entity.CompanyInfo oCompanyInfo = new Entity.CompanyInfo(); oCompanyInfo.StrCategoryLevel = ddlLevel.SelectedItem.Text; oCompanyInfo.StrUserName = AppLib.GetLoggedInUserName(); oCompanyInfo.StrCategoryName = ddlCategory.SelectedItem.Text; oCompanyInfo.StrQuestion = txtQuestion.Text.Trim(); oCompanyInfo.StrSubject = txtsubject.Text.Trim(); oCompanyInfo.StrComments = txtComments.Text.Trim(); oCompanyInfo.StrQuestionType = rdoQuestionType.SelectedItem.Text; oCompanyInfo.StrAnswer = string.Empty; oCompanyInfo.DtCreatedOn = DateTime.Now; oCompanyInfo.ChrIsAnsweredGiven = 'N'; oCompanyInfo.ChrIsDeleted = 'N'; BLL.CompanyManager oCompanyManager = new BLL.CompanyManager(); oCompanyManager.SaveQuestionToHealthCoachFromUser(oCompanyInfo); lblMsg.Text = "Your question has been successfully sent to health coach. Health Coach will approach you soon"; strContent = strContent.Replace("[Name]", oCompanyManager.GetUserDetailsByUsername(AppLib.GetLoggedInUserName()).StrUserFullName); strContent = strContent.Replace("[Email]", AppLib.GetLoggedInUserName()); strContent = strContent.Replace("[Category]", ddlCategory.SelectedItem.Text + " - " + ddlLevel.SelectedItem.Text); strContent = strContent.Replace("[Question]", txtQuestion.Text.Trim()); strContent = strContent.Replace("[Subject]", txtsubject.Text); strContent = strContent.Replace("[Comments]", txtComments.Text); strContent = strContent.Replace("[QuestionType]", rdoQuestionType.SelectedItem.Text); strContent = strContent.Replace("[SiteUrl]", AppConfig.GetBaseSiteUrl()); strContent = strContent.Replace("[SiteName]", AppConfig.GetSiteName()); strContent = strContent.Replace("[SitePhone]", AppConfig.GetSITEPHONE()); ///Code for Forum if (rdoQuestionType.SelectedValue == "Public") { string msg = txtQuestion.Text; msg = msg.Trim(); msg = msg.Replace("<", "<").Replace(">", ">"); msg = aspnetforum.Utils.Formatting.FilterBadWords(msg); bool isModer = false; if (msg == "") return; string catgory = ddlCategory.SelectedItem.Text; string lvl = ddlLevel.SelectedValue; string totalvalue = catgory + " - " + lvl; providerFactory = aspnetforum.Utils.DB.CreateDBProviderFactory(); cn = aspnetforum.Utils.DB.CreateConnection(); cmd = providerFactory.CreateCommand(); cmd.Connection = cn; this.cn.Open(); this.cmd.CommandText = "SELECT ForumID FROM Forums WHERE Title='" + totalvalue + "'"; object _forumID = this.cmd.ExecuteScalar(); if (_addTopic || _changeTopic) //creating a new topic or editing topic title { string subj = txtsubject.Text; subj = subj.Trim(); if (subj == "") return; subj = aspnetforum.Utils.Formatting.FilterBadWords(subj); if (_addTopic) { string CurrentUserID = Membership.GetUser().ProviderUserKey.ToString(); this.cmd.Parameters.Clear(); this.cmd.CommandText = "INSERT INTO ForumTopics (ForumID, UserID, Subject, Visible) " + "VALUES (?, convert(uniqueidentifier, ?), ?, ?)"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, _forumID, CurrentUserID, subj, !_premoderated); this.cmd.ExecuteNonQuery(); this.cmd.Parameters.Clear(); this.cmd.CommandText = "SELECT MAX(TopicID) FROM ForumTopics WHERE Subject=?"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, subj); _topicID = Convert.ToInt32(this.cmd.ExecuteScalar()); this.cmd.Parameters.Clear(); //CREATE A POLL (if specified) string pollQuestion = "";//tbPollQuestion.Text.Trim(); if (pollQuestion.Length > 0) { //add a poll this.cmd.Parameters.Clear(); this.cmd.CommandText = "INSERT INTO ForumPolls (TopicID, Question) " + "VALUES (?, ?)"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, _topicID, pollQuestion); this.cmd.ExecuteNonQuery(); this.cmd.Parameters.Clear(); //now get the ID of the poll we just created this.cmd.CommandText = "SELECT MAX(PollID) FROM ForumPolls WHERE TopicID=?"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, _topicID); int pollID = Convert.ToInt32(this.cmd.ExecuteScalar()); this.cmd.Parameters.Clear(); int i = 0; while (Request.Form["PollOption" + i] != null && Request.Form["PollOption" + i].Trim().Length > 0) { //add option this.cmd.CommandText = "INSERT INTO ForumPollOptions (PollID, OptionText) " + "VALUES (?, ?)"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, pollID, Request.Form["PollOption" + i]); this.cmd.ExecuteNonQuery(); this.cmd.Parameters.Clear(); i++; } } } else if (_changeTopic) //edit topic subj { this.cmd.Parameters.Clear(); this.cmd.CommandText = "UPDATE ForumTopics SET Subject = ? WHERE TopicID = ?"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, subj, _topicID); this.cmd.ExecuteNonQuery(); this.cmd.Parameters.Clear(); } } // Inserting or updating? if (_isEditing) { //if moderatro, admin or message author //if (bmoderator || _messageAuthorID == CurrentUserID) //{ // // Record last editor and date at the end of the message. // msg += "\r\n<em>edited by " + Session["aspnetforumUserName"] + // " on " + aspnetforum.Utils.GetCurrTime().ToShortDateString() + "</em>"; // this.cmd.CommandText = "UPDATE ForumMessages SET Body=?, Visible=? " + // "WHERE MessageID=" + _messageId; // aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, msg, !_premoderated); // this.cmd.ExecuteNonQuery(); // this.cmd.Parameters.Clear(); // SaveAttachments(_messageId); //} } else //inserting { this.cmd.CommandText = "INSERT INTO ForumMessages (TopicID, UserID, Body, CreationDate, Visible) " + "VALUES (?, convert(uniqueidentifier, ?), ?, ?, ?)"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, _topicID, CurrentUserID, msg, aspnetforum.Utils.GetCurrTime(), !_premoderated); this.cmd.ExecuteNonQuery(); this.cmd.Parameters.Clear(); //incrementing repliescount (well... actually - re-calculating it) this.cmd.CommandText = "SELECT Count(MessageID) FROM ForumMessages WHERE TopicID=" + _topicID; object res = this.cmd.ExecuteScalar(); this.cmd.CommandText = "UPDATE ForumTopics SET RepliesCount=" + res.ToString() + " WHERE TopicID=" + _topicID; this.cmd.ExecuteNonQuery(); //incrementing PostsCount in Users table only for new messages, not edits. //only for registered users (if guestmode is on) if (CurrentUserID != "") { this.cmd.CommandText = "UPDATE ForumUsers SET PostsCount=PostsCount+1 WHERE UserID='" + CurrentUserID + "'"; this.cmd.ExecuteNonQuery(); } //get last message's ID this.cmd.CommandText = "SELECT MAX(MessageID) FROM ForumMessages WHERE TopicID=" + _topicID + " AND UserID='" + CurrentUserID + "'"; aspnetforum.Utils.DB.FillCommandParamaters(ref cmd, true); res = this.cmd.ExecuteScalar(); int lastmsgid = (res == null || res == DBNull.Value) ? 0 : Convert.ToInt32(res); //updating LastMessageID in Topics table this.cmd.CommandText = "UPDATE ForumTopics SET LastMessageID=" + lastmsgid + " WHERE TopicID=" + _topicID; this.cmd.ExecuteNonQuery(); SaveAttachments(lastmsgid); } //saving notifications settings if (_mailNotificationsEnabled) { this.cmd.CommandText = "DELETE FROM ForumSubscriptions WHERE UserID='" + CurrentUserID + "' AND TopicID=" + _topicID; this.cmd.ExecuteNonQuery(); //if(this.cbSubscribe.Checked) //{ this.cmd.CommandText = "INSERT INTO ForumSubscriptions (UserID, TopicID) VALUES ('" + CurrentUserID + "', " + _topicID + ")"; this.cmd.ExecuteNonQuery(); //} } //sending notifications if (_mailNotificationsEnabled) { string url = Request.Url.ToString().ToLower(); url = url.Substring(0, url.IndexOf("addpost.aspx")); if (_addTopic) { if (_forumID != null) { aspnetforum.SendNotifications.SendNewTopicNotificationEmails(Convert.ToInt32(_forumID), CurrentUserID, url); } } else { aspnetforum.SendNotifications.SendNewMsgNotificationEmails(_topicID, CurrentUserID, url); } } } try { AppLib.SendMailToUser(AppLib.GetLoggedInUserName(), txtsubject.Text, strContent, AppConfig.GetAdminEmail()); AppLib.SendMailToUser(AppConfig.GetAdminEmail(), txtsubject.Text, "Dear Admin, Following are the contents that were sent to the user. <br />" + strContent, AppLib.GetLoggedInUserName()); } catch { } oTemplateLib = null; oCompanyManager = null; oCompanyInfo = null; txtQuestion.Text = string.Empty; txtsubject.Text = string.Empty; txtComments.Text = string.Empty; ; rdoQuestionType.SelectedIndex = 0; }