public virtual System.Data.DataSet GetDataSet(System.Data.IDbCommand cmd)
{
string datasetName = "NewDataSet";
System.Data.DataSet ds = new System.Data.DataSet(datasetName);
using (System.Data.IDbConnection idbc = this.Connection)
{
try
{
cmd.Connection = idbc;
using (System.Data.Common.DbDataAdapter daQueryTable = this.m_factory.CreateDataAdapter())
{
daQueryTable.SelectCommand = (System.Data.Common.DbCommand)cmd;
daQueryTable.Fill(ds);
} // End Using daQueryTable
} // End Try
catch (System.Data.Common.DbException ex)
{
//if (Log("cDAL.GetDataTable(System.Data.IDbCommand cmd)", ex, cmd.CommandText))
throw;
}// End Catch
finally
{
if (idbc.State != System.Data.ConnectionState.Closed)
{
idbc.Close();
}
} // End Finally
} // End Using idbc
return(ds);
} // End Function GetDataSet
/// <summary>
/// Insert Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select SQL</param>
/// <param name="pDataReader">Data Reader</param>
/// <returns>bool</returns>
public static bool InsertData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
try
{
Conn.Open();
cmd = builder.GetInsertCommand(true);
cmd.CommandTimeout = 1500;
cmd.Connection = Conn;
//builder.GetInsertCommand((true);
while (pDataReader.Read())
{
foreach (System.Data.Common.DbParameter param in cmd.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
if (FieldName.ToUpperInvariant() == "UNIQUEKEY")
{
param.Value = null;
}
else
{
param.Value = pDataReader[FieldName];
}
}
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
}
result = true;
return(result);
}
/// <summary>
/// 根据 sql 语句，查询默认数据库，返回一个 DataTable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable RunTable(String sql)
{
using (IDbConnection cn = db.getConnection()) {
DataTable dataTable = new DataTable();
System.Data.Common.DbDataAdapter adapter = DataFactory.GetAdapter(sql, cn);
adapter.Fill(dataTable);
return(dataTable);
}
}
public override DataTable SelectBySql(string strSql)
{
DataTable datatable = new DataTable();
System.Data.Common.DbDataAdapter oraAdapter = this.CreateDbDataAdapter() as System.Data.Common.DbDataAdapter;;
System.Data.IDbCommand command = this.CreateDbCommand(strSql);
(oraAdapter as IDbDataAdapter).SelectCommand = command;
oraAdapter.Fill(datatable);
return(datatable);
}
public static System.Data.DataTable GetDataTable(string SQL)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (System.Data.Common.DbDataAdapter da = GetAdapter(SQL))
{
da.Fill(dt);
}
return(dt);
}
/// <summary>
/// Insert Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select Statement</param>
/// <param name="pDataRows">DataRows</param>
/// <returns>bool</returns>
public static bool InsertData(string pFileString, string pSelectSQL, System.Data.DataRow[] pDataRows)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
try
{
Conn.Open();
//cmd = builder.GetInsertCommand(true);
cmd = Conn.CreateCommand();
cmd.CommandTimeout = 1500;
//builder.GetInsertCommand((true);
foreach (DataRow R in pDataRows)
{
//string SQL = GetInsertSQL(pSelectSQL.Substring(pSelectSQL.LastIndexOf (" From ") + 6, pSelectSQL.Length - pSelectSQL.LastIndexOf(" From ") - 6),R);
string SQL = GetInsertSQL(R.Table.TableName, R);
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
}
result = true;
return(result);
}
/// <summary>
/// 填充 DataSet
/// </summary>
/// <param name="ds">目标 DataSet</param>
/// <returns></returns>
public int Fill(System.Data.DataSet ds)
{
int nRtn = 0;
Common.DbConnectionHelper ConnectionHelper = new Common.DbConnectionHelper(Connection);
System.Data.Common.DbDataAdapter Adapter = ConnectionHelper.CreateAdapter();
ConnectionHelper.Open();
#region 表
Adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}",
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables)
);
nRtn += Adapter.Fill(ds, Apq_Tables);
#endregion
#region 列
Adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}",
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns)
);
nRtn += Adapter.Fill(ds, Apq_Columns);
#endregion
return(nRtn);
}
/*
* Public Sub Bind()
* If _Cmd Is Nothing Or _Ad Is Nothing Then
* Throw New Exception("The database connection is not set")
* End If
* _Ad.Fill(Me)
* End Sub
*/
/// <summary>
/// Executes the query and fills this datatable.
/// </summary>
public void Bind()
{
if (_Cmd == null || _Ad == null)
{
throw (new TenorException("The database connection is not set"));
}
bool exception = false;
DateTime sqlTime = DateTime.Now;
try
{
if (_ActiveConnection.State == ConnectionState.Closed)
{
Helper.OpenConnection(_ActiveConnection);
}
sqlTime = DateTime.Now;
//Diagnostics.Debug.DebugSQL("Tenor.Data.DataTable", this.CommandText, null, currentConnection);
_Ad.Fill(this);
}
catch (Exception up)
{
exception = true;
up.Data.Add("CommandText", CommandText);
throw;
}
finally
{
try
{
if (exception)
{
System.Diagnostics.Trace.TraceInformation("DataTable.Bind Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec. (SqlException)");
}
else
{
System.Diagnostics.Trace.TraceInformation("DataTable.Bind Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec.");
}
}
catch
{
}
if (_ActiveConnection.State != ConnectionState.Closed)
{
_ActiveConnection.Close();
}
}
}
/// <summary>
/// Update Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select Statement</param>
/// <param name="pDataReader">DataReader</param>
/// <returns>bool</returns>
public static bool UpdateData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
}
try
{
Conn.Open();
cmd = builder.GetInsertCommand(true);
cmd.CommandTimeout = 1500;
while (pDataReader.Read())
{
foreach (System.Data.Common.DbParameter param in cmd.Parameters)
{
param.Value = pDataReader[param.SourceColumn];
}
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
result = true;
return(result);
}
//public static System.Data.DataTable ExecuteStoredProcedure(this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade
// )
//{
// return ExecuteStoredProcedure(databaseFacade, "foo", ("abc", "def"), ("abc", 5));
//}
public static System.Data.DataTable ExecuteStoredProcedure(
this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade,
string procedureName,
params System.ValueTuple <string, object>[] parameters
)
{
System.Data.DataTable dt = new System.Data.DataTable();
// using (System.Data.Common.DbConnection con = databaseFacade.GetDbConnection())
System.Data.Common.DbConnection con = databaseFacade.GetDbConnection();
{
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = procedureName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; ++i)
{
System.Data.Common.DbParameter p = cmd.CreateParameter();
p.ParameterName = parameters[i].Item1;
p.Value = parameters[i].Item2;
p.DbType = GetDbType(p.Value);
if (p.Value == null)
{
p.Value = System.DBNull.Value;
} // End if (p.Value == null)
cmd.Parameters.Add(p);
} // Next i
} // End if (parameters != null)
// cmd.ExecuteNonQuery();
// cmd.ExecuteReader();
using (System.Data.Common.DbDataAdapter da = System.Data.Common.ProviderExtensions.CreateDataAdapter(con, cmd))
{
da.Fill(dt);
} // End Using da
} // End Using cmd
} // End Using con
return(dt);
} // End Function ExecuteStoredProcedure
private static System.Data.DataTable GetDataSourceData(System.Data.Common.DbDataAdapter adapter, System.String namecolumn, System.String mailcolumn, System.String ownercolumn, System.String book)
{
if (adapter == null)
{
return(null);
}
System.Data.DataTable data = GetDataSourceDataTable(namecolumn, mailcolumn, ownercolumn, book);
try {
adapter.Fill(data);
} catch (System.Exception e) {
if (log.IsErrorEnabled)
{
log.Error("Error while doing query", e);
}
return(null);
}
return(data);
}
public static System.Data.DataTable GetDataTable(System.Data.Common.DbCommand cmd)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (System.Data.Common.DbConnection con = GetConnection())
{
cmd.Connection = con;
using (System.Data.Common.DbDataAdapter da = fact.CreateDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
}
return(dt);
}
private DataTable getSynonymDataTable(string synonymDb, string sql)
{
DataTable schemaDataTable = new DataTable();
if (synonymDb != "")
{
try
{
System.Data.Common.DbConnection Con;
string ConectionString = @"Server=" + server +
";User Id=" + userId +
";Password="******";Database=" + synonymDb + ";";
Con = new SqlConnection(ConectionString);
Con.Open();
System.Data.Common.DbCommand vlCommand = default(System.Data.Common.DbCommand);
System.Data.Common.DbDataAdapter vlAdapter = default(System.Data.Common.DbDataAdapter);
vlCommand = Con.CreateCommand();
vlCommand.CommandText = sql;
vlAdapter = getDbDataAdapter();
vlAdapter.SelectCommand = vlCommand;
vlAdapter.Fill(schemaDataTable);
Con.Close();
}
catch
{
}
}
return(schemaDataTable);
}
} // End Function GetDataTable
public virtual System.Data.DataTable GetDataTable(System.Data.IDbCommand cmd)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (System.Data.Common.DbConnection idbc = GetConnection())
{
lock (idbc)
{
lock (cmd)
{
try
{
cmd.Connection = idbc;
using (System.Data.Common.DbDataAdapter daQueryTable = m_fact.CreateDataAdapter())
{
daQueryTable.SelectCommand = (System.Data.Common.DbCommand)cmd;
daQueryTable.Fill(dt);
} // End Using daQueryTable
} // End Try
catch (System.Data.Common.DbException ex)
{
if (Log("SQL.GetDataTable(System.Data.IDbCommand cmd)", ex, cmd))
{
throw;
}
}// End Catch
finally
{
if (idbc.State != System.Data.ConnectionState.Closed)
{
idbc.Close();
}
} // End Finally
} // End lock cmd
} // End lock idbc
} // End Using idbc
return(dt);
} // End Function GetDataTable
public static System.Data.DataTable GetDataTable(System.Data.IDbCommand cmd)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (System.Data.IDbConnection idbConn = GetConnection())
{
cmd.Connection = idbConn;
try
{
using (System.Data.Common.DbDataAdapter da = s_factory.CreateDataAdapter())
{
da.SelectCommand = (System.Data.Common.DbCommand)cmd;
da.Fill(dt);
}
}
catch
{
throw;
}
}
return(dt);
}
public static void DataToXML()
{
string table_schema = "geoip";
string table_name = "geoip_blocks_temp";
table_schema = "public";
table_name = "t_sys_language_monthnames";
using (System.Data.DataTable dt = new System.Data.DataTable())
{
dt.TableName = "record";
using (System.Data.DataSet ds = new System.Data.DataSet(table_name))
{
ds.Tables.Add(dt);
// dt.Namespace = "foo";
using (System.Data.Common.DbConnection con = Npgsql.NpgsqlFactory.Instance.CreateConnection())
{
con.ConnectionString = GetCS();
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM " + table_schema + "." + table_name;
using (System.Data.Common.DbDataAdapter da = Npgsql.NpgsqlFactory.Instance.CreateDataAdapter())
{
da.SelectCommand = cmd;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
da.Fill(dt);
if (con.State != System.Data.ConnectionState.Open)
{
con.Close();
}
} // End Using da
} // End Using cmd
} // End Using con
string exportFilename = System.IO.Path.Combine(@"d:\", table_name + ".xml");
//using (System.IO.Stream fs = System.IO.File.OpenWrite(exportFilename))
//{
// using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8))
// {
// // System.IO.StringWriter sw = new System.IO.StringWriter();
// // dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
// dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
// } // End Using sw
//} // End Using fs
System.Xml.XmlWriterSettings xs = new System.Xml.XmlWriterSettings();
xs.Indent = true;
xs.IndentChars = " ";
xs.NewLineChars = System.Environment.NewLine;
xs.OmitXmlDeclaration = false;
// xs.Encoding = System.Text.Encoding.UTF8; // doesn't work with pgsql
xs.Encoding = new System.Text.UTF8Encoding(false);
// <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(exportFilename, xs))
{
dt.WriteXml(writer, System.Data.XmlWriteMode.IgnoreSchema);
} // End Using writer
System.Console.WriteLine(dt.Rows.Count);
} // End Using ds
} // End Using dt
} // End Sub DataToXML