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;
}