|
public static DbConnectionFromConnectionString ( string connectionString ) : |
||
| connectionString | string | |
| return | ||
public object ExecuteScalar(string connectionString, string sql)
{
DbConnection connection = Helper.DbConnectionFromConnectionString(connectionString);
connection.Open();
DbCommand command = GetDbCommand(connection);
command.CommandText = sql;
var result = command.ExecuteScalar();
connection.Close();
return(result);
}
public object ExecuteScalar(ConnectionType connectionType, string connectionString, string sql)
{
object result = null;
var connection = Helper.DbConnectionFromConnectionString(connectionType, connectionString);
try
{
connection.Open();
DbCommand command = GetDbCommand(connection);
command.CommandText = sql;
result = command.ExecuteScalar();
}
finally
{
connection.Close();
}
return(result);
}
public void ExecuteNonQuery(string connectionString, string sql, string commandsSeparator = null)
{
DbConnection connection = Helper.DbConnectionFromConnectionString(connectionString);
connection.Open();
DbCommand command = GetDbCommand(connection);
string[] commandTexts = new string[] { sql };
if (!string.IsNullOrEmpty(commandsSeparator))
{
commandTexts = sql.Split(new string[] { commandsSeparator }, StringSplitOptions.RemoveEmptyEntries);
}
foreach (var commandText in commandTexts)
{
command.CommandText = commandText;
command.ExecuteNonQuery();
}
connection.Close();
}
public DataTable LoadDataTable(string connectionString, string sql)
{
DataTable table = new DataTable();
DbDataAdapter adapter = null;
var connection = Helper.DbConnectionFromConnectionString(connectionString);
connection.Open();
if (connection is OdbcConnection)
{
adapter = new OdbcDataAdapter(sql, (OdbcConnection)connection);
}
else
{
adapter = new OleDbDataAdapter(sql, (OleDbConnection)connection);
}
adapter.SelectCommand.CommandTimeout = SelectTimeout;
adapter.Fill(table);
return(table);
}
public DataTable LoadDataTable(ConnectionType connectionType, string connectionString, string sql)
{
DataTable table = new DataTable();
try
{
if (MyLoadDataTable != null)
{
return(MyLoadDataTable(connectionType, connectionString, sql));
}
var connection = Helper.DbConnectionFromConnectionString(connectionType, connectionString);
try
{
connection.Open();
if (UseDbDataAdapter)
{
DbDataAdapter adapter = null;
if (connection is OdbcConnection)
{
adapter = new OdbcDataAdapter(sql, (OdbcConnection)connection);
}
else if (connection is SqlConnection)
{
adapter = new SqlDataAdapter(sql, (SqlConnection)connection);
}
else
{
adapter = new OleDbDataAdapter(sql, (OleDbConnection)connection);
}
adapter.SelectCommand.CommandTimeout = SelectTimeout;
adapter.Fill(table);
}
else
{
DbCommand cmd = null;
if (connection is OdbcConnection)
{
cmd = new OdbcCommand(sql, (OdbcConnection)connection);
}
else if (connection is SqlConnection)
{
cmd = new SqlCommand(sql, (SqlConnection)connection);
}
else
{
cmd = new OleDbCommand(sql, (OleDbConnection)connection);
}
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
DbDataReader dr = cmd.ExecuteReader();
DataTable schemaTable = dr.GetSchemaTable();
foreach (DataRow dataRow in schemaTable.Rows)
{
DataColumn dataColumn = new DataColumn();
dataColumn.ColumnName = dataRow["ColumnName"].ToString();
dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
dataColumn.Unique = (bool)dataRow["IsUnique"];
for (int i = 0; i < table.Columns.Count; i++)
{
if (dataColumn.ColumnName == table.Columns[i].ColumnName)
{
dataColumn.ColumnName += "_" + table.Columns.Count.ToString();
}
}
table.Columns.Add(dataColumn);
}
while (dr.Read())
{
DataRow dataRow = table.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
dataRow[i] = dr[i];
}
table.Rows.Add(dataRow);
}
dr.Close();
}
}
finally
{
connection.Close();
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error got when executing '{0}':\r\n{1}\r\n", sql, ex.Message));
}
return(table);
}
public DataTable LoadDataTable(string connectionString, string sql)
{
if (MyLoadDataTable != null)
{
return(MyLoadDataTable(connectionString, sql));
}
DataTable table = new DataTable();
if (UseDbDataAdapter)
{
DbDataAdapter adapter = null;
var connection = Helper.DbConnectionFromConnectionString(connectionString);
connection.Open();
if (connection is OdbcConnection)
{
adapter = new OdbcDataAdapter(sql, (OdbcConnection)connection);
}
else
{
adapter = new OleDbDataAdapter(sql, (OleDbConnection)connection);
}
adapter.SelectCommand.CommandTimeout = SelectTimeout;
adapter.Fill(table);
}
else
{
DbCommand cmd = new OdbcCommand();
var connection = Helper.DbConnectionFromConnectionString(connectionString);
connection.Open();
if (connection is OdbcConnection)
{
cmd = new OdbcCommand(sql, (OdbcConnection)connection);
}
else
{
cmd = new OleDbCommand(sql, (OleDbConnection)connection);
}
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
DbDataReader dr = cmd.ExecuteReader();
DataTable schemaTable = dr.GetSchemaTable();
foreach (DataRow dataRow in schemaTable.Rows)
{
DataColumn dataColumn = new DataColumn();
dataColumn.ColumnName = dataRow["ColumnName"].ToString();
dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
dataColumn.Unique = (bool)dataRow["IsUnique"];
for (int i = 0; i < table.Columns.Count; i++)
{
if (dataColumn.ColumnName == table.Columns[i].ColumnName)
{
dataColumn.ColumnName += "_" + table.Columns.Count.ToString();
}
}
table.Columns.Add(dataColumn);
}
while (dr.Read())
{
DataRow dataRow = table.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
dataRow[i] = dr[i];
}
table.Rows.Add(dataRow);
}
}
return(table);
}