| public Load ( IDataReader reader, LoadOption loadOption ) : void | ||
| reader | IDataReader | |
| loadOption | LoadOption | |
| return | void | |
protected DataSet.DSParameter baseUpdate(DataSet.DSParameter ds, string tableName)
{
if (_is_Single_Transaction)
{
try
{
_base.BeginTransaction();
_base.SetConnection();
_db = _base.GetDatabase();
ds.Load(_base.Update(ds), LoadOption.OverwriteChanges, tableName);
_base.CommitTransaction();
}
catch (Exception exp)
{
_base.RollBackTransaction();
throw exp;
}
finally
{
}
return ds;
}
_base.SetConnection();
_db = _base.GetDatabase();
ds.Load(_base.Update(ds), LoadOption.OverwriteChanges, tableName);
return ds;
}
private void InitializeGrid( )
{
CommonUtils.ConexionBD.AbrirConexion( );
string sqlProductData = "SELECT Producto.ProductoId, Producto.Imagen, Producto.Nombre, Producto.Descripcion, Producto.Tipo, " +
"Categoria.Tipo AS [Categoria], Producto.Tamano AS Tamaño, Producto.PrecioVenta AS [Precio de Venta], Producto.RecetaId, Producto.Mostrar AS [Visible] From Producto, Categoria "+
"WHERE Producto.CategoriaId=Categoria.CategoriaId";
string sqlProductCombo = "SELECT Producto.Nombre, Producto_Producto.ComboId FROM Producto INNER JOIN Producto_Producto ON Producto.ProductoId = Producto_Producto.ProductoId";
try
{
SqlCommand cmdProduct = new SqlCommand( sqlProductData , CommonUtils.ConexionBD.Conexion );
SqlCommand cmdCombo = new SqlCommand( sqlProductCombo , CommonUtils.ConexionBD.Conexion );
DataSet dataSet = new DataSet( );
dataSet.Load( cmdProduct.ExecuteReader( ) , LoadOption.OverwriteChanges , "Producto" );
dataSet.Load( cmdCombo.ExecuteReader( ) , LoadOption.OverwriteChanges , "Combo" );
dataSet.Relations.Add( "Combo" ,
dataSet.Tables[ "Producto" ].Columns[ "ProductoId" ] ,
dataSet.Tables[ "Combo" ].Columns[ "ComboId" ] , false );
gridProductos.DataSource = dataSet.Tables[ "Producto" ];
ColumnView view = this.gridProductos.MainView as ColumnView;
view.Columns.Add( );
view.ActiveFilterEnabled = true;
DevExpress.XtraEditors.Repository.RepositoryItemPictureEdit rPic = new DevExpress.XtraEditors.Repository.RepositoryItemPictureEdit( );
rPic.NullText = "No Image";
rPic.SizeMode = DevExpress.XtraEditors.Controls.PictureSizeMode.Squeeze;
view.Columns[ 1 ].ColumnEdit = rPic;
view.Columns[ 1 ].GetBestWidth( );
view.Columns[ 8 ].Visible = false;
view.Columns[9].Visible = true;
this.gridProductos.RepositoryItems.Add( view.Columns[ 1 ].ColumnEdit );
MessageBarValue = dataSet.Tables[ "Producto" ].Rows.Count.ToString( ) + " Items";
}
catch ( Exception ex )
{
log.Error( ex.Message , ex );
MessageBarValue = "No se pudieron listar los productos. Hubo un error: " + ex.Message;
}
finally
{
barItem.Caption = MessageBarValue;
}
CommonUtils.ConexionBD.CerrarConexion( );
}
/// <summary>
/// Exports the Gallery Server Pro data in the current database to an XML-formatted string.
/// </summary>
/// <param name="exportMembershipData">if set to <c>true</c> export membership data.</param>
/// <param name="exportGalleryData">if set to <c>true</c> export gallery data.</param>
/// <returns>Returns an XML-formatted string containing the gallery data.</returns>
internal static string ExportData(bool exportMembershipData, bool exportGalleryData)
{
using (DataSet ds = new DataSet("GalleryServerData"))
{
System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
using (System.IO.Stream stream = asm.GetManifestResourceStream("GalleryServerPro.Data.SqlCe.GalleryServerProSchema.xml"))
{
ds.ReadXmlSchema(stream);
}
using (SqlCeConnection cn = Util.GetDbConnectionForGallery())
{
using (SqlCeCommand cmd = cn.CreateCommand())
{
cn.Open();
if (exportMembershipData)
{
foreach (string tableName in _schemaMembershipTableNames)
{
cmd.CommandText = String.Concat(@"SELECT * FROM ", tableName, ";");
ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, tableName);
}
}
if (exportGalleryData)
{
foreach (string tableName in _galleryTableNames)
{
cmd.CommandText = String.Concat(@"SELECT * FROM ", tableName, ";");
ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, tableName);
}
}
// We always want to get the schema into the dataset, even when we're not getting the rest of the gallery data.
cmd.CommandText = @"SELECT SettingValue AS SchemaVersion FROM gs_AppSetting WHERE SettingName='DataSchemaVersion';";
ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "gs_SchemaVersion");
using (System.IO.StringWriter sw = new System.IO.StringWriter(CultureInfo.InvariantCulture))
{
ds.WriteXml(sw, XmlWriteMode.WriteSchema);
//ds.WriteXmlSchema(@"D:\gsp_schema.xml"); // Use to create new schema file after a database change
return sw.ToString();
}
}
}
}
}
private void InitializeGrid( )
{
CommonUtils.ConexionBD.AbrirConexion( );
string sqlUsuarios = "SELECT UsuarioId, Nombre, Apellidos, Login, Password, Telefono, PrivilegiosXML " +
"From UsuariosSistema ";
try
{
SqlCommand cmdProduct = new SqlCommand( sqlUsuarios , CommonUtils.ConexionBD.Conexion );
DataSet dataSet = new DataSet( );
dataSet.Load( cmdProduct.ExecuteReader( ) , LoadOption.OverwriteChanges , "Usuarios" );
gridControlUsuarios.DataSource = dataSet.Tables[ "Usuarios" ];
MessageBarValue = dataSet.Tables[ "Usuarios" ].Rows.Count.ToString( ) + " Usuarios";
}
catch ( Exception ex )
{
log.Error( ex.Message , ex );
MessageBarValue = "No se pudieron listar los productos. Hubo un error: " + ex.Message;
}
finally
{
barItem.Caption = MessageBarValue;
}
CommonUtils.ConexionBD.CerrarConexion( );
}
public static DataSet ReportAssaysResult()
{
DataSet ds = new DataSet("ReportResult");
using (new TransactionScope(TransactionScopeOption.Suppress))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
//////Get Curves
cmd.CommandText = "[X_SP_GetAssaysByWorkflow]";
//cmd.CommandText = "[dbo].[X_SP_GetAssays]";
cmd.CommandType = CommandType.StoredProcedure;
var parm1 = cmd.CreateParameter();
parm1.ParameterName = "@assay_group_projectid";
parm1.DbType = DbType.Guid;
parm1.Value = new Guid("31B4CCEC-A72C-4F30-A13A-32B48762FDD9");
//cmd.Parameters.Add(parm1);
try
{
//Let's actually run the queries
cmd.Connection.Open();
cmd.CommandTimeout = 600; //10 mins
var reader = cmd.ExecuteReader();
ds.Load(reader, LoadOption.OverwriteChanges, "t");
}
finally
{
cmd.Connection.Close();
}
return ds;
}
}
public static DataSet ExecuteDatasetTEST(string strConnection, string commandText)
{
if (strConnection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
DbConnection conn = new TAdoDbxInterBaseConnection();
conn.ConnectionString = strConnection;
DataSet dsReturnin = new DataSet();
DataTable dt = new DataTable();
string sql = commandText;
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
conn.Open();
DbDataReader myreader = cmd.ExecuteReader();
dsReturnin.Tables.Add(dt);
dsReturnin.Load(myreader, LoadOption.PreserveChanges, dsReturnin.Tables[0]);
myreader.Close();
conn.Close();
conn.Dispose();
return dsReturnin;
}
protected void UploadAs_Click(object sender, EventArgs e)
{
if (DataSetName.Text.Trim().Length != 0 && DataSetUpload.HasFile)
{
//move most to uploader
//handle spaces in file name
String uploadPath = System.IO.Path.Combine(System.IO.Path.GetTempPath().ToString(), DataSetUpload.FileName);
DataSetUpload.SaveAs(uploadPath);
String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetTempPath().ToString() + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";
connection = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand("SELECT * FROM " + DataSetUpload.FileName, connection);
da = new OleDbDataAdapter(cmd);
connection.Open();
dt = new System.Data.DataTable();
da.Fill(dt);
System.Data.DataSet ds = new System.Data.DataSet(DataSetName.Text.Trim());
String[] parameters = { "main" };
ds.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges, parameters);
Session.Add("table", dt);
Session.Add("connection", connection);
Session.Add("adapter", da);
Registry.Registry registry = Registry.Registry.getRegistry(Session);
registry.registerDataset(ds);
DatasetList.Items.Add(ds.DataSetName);
}
}
private void InitDataSource( )
{
CommonUtils.ConexionBD.AbrirConexion( );
ChartTitle chartTitle = new ChartTitle( );
chartTitle.Text = "Productos por tipo";
chartControl.Titles.Add( chartTitle );
//chartControl.SeriesTemplate.ValueDataMembersSerializable = "Tipo";
string sql = "SELECT Categoria.Tipo, COUNT(Categoria.Tipo) AS Amount FROM Categoria INNER JOIN Producto ON Categoria.CategoriaId = Producto.CategoriaId " +
"GROUP BY Categoria.Tipo HAVING (Categoria.Tipo = Categoria.Tipo)";
//DataTable table = CommonUtils.ConexionBD.EjecutarConsulta( sql );
SqlCommand cmdProduct = new SqlCommand( sql , CommonUtils.ConexionBD.Conexion );
DataSet dataSet = new DataSet( );
dataSet.Load( cmdProduct.ExecuteReader( ) , LoadOption.OverwriteChanges , "Producto" );
BindingSource bs = new BindingSource( );
bs.DataSource = dataSet.Tables[ 0 ];
chartControl.DataSource = bs;
chartControl.SeriesDataMember = "Tipo";
chartControl.SeriesTemplate.ArgumentDataMember = "Amount";
//chartControl.SeriesTemplate.ValueDataMembersSerializable = "Amount";
chartControl.SeriesTemplate.ValueDataMembers.AddRange( new string[ ] { "Amount" } );
CommonUtils.ConexionBD.CerrarConexion( );
}
/// <summary>Executes a SQL Stored Procedure with 1 parameter, and returns a DataSet.</summary>
/// <param name="StoredProcName">The actual name of the Stored Procedure on the SQL Server</param>
/// <param name="ParamValue">The Valure being passed to the Stored Procedure.</param>
/// <param name="ParamName">The actual name of the Paramter in the Stored Procedure.</param>
/// <returns>DataSet</returns>
public DataSet SelectQuery(string StoredProcName, object ParamValue, string ParamName)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
DataSet ds1 = new DataSet();
// create and open a connection object
//conn = new SqlConnection(@"Data Source=NEWDUCK\SQLEXPRESSR2;Initial Catalog=ARC_ORG;Integrated Security=True");
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Home_ARC_ORGConnectionString"].ToString());
conn.Open();
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(StoredProcName, conn);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter(ParamName, ParamValue));
// execute the command
//SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//adapter.Fill(ds1);
// iterate through results, printing each to console
rdr = cmd.ExecuteReader();
ds1.Load(rdr, LoadOption.OverwriteChanges, "eNewLetter");
//while (rdr.Read())
//{
//}
conn.Close();
return ds1;
}
public DataTable ExecuteQuery(string query, string dataTableName, SqlParameter[] parameters)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = query;
command.CommandTimeout = commandExecutionTimeout;
command.CommandType = CommandType.Text;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
using (var reader = command.ExecuteReader())
{
var dataSet = new DataSet();
var table = new DataTable(dataTableName);
dataSet.Tables.Add(table);
dataSet.Load(reader, LoadOption.OverwriteChanges, table);
return table;
}
}
}
}
/// <summary>
/// Exports the data from the SQL Server database to <paramref name="filePath"/>.
/// </summary>
/// <param name="filePath">The file name (including the path) to which to write.</param>
/// <param name="exportMembershipData">if set to <c>true</c> export membership data.</param>
/// <param name="exportGalleryData">if set to <c>true</c> export gallery data.</param>
internal static void ExportData(string filePath, bool exportMembershipData, bool exportGalleryData)
{
DataSet ds = new DataSet("GalleryServerData");
System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
using (System.IO.Stream stream = asm.GetManifestResourceStream("GalleryServerPro.Data.SqlServer.GalleryServerProSchema.xml"))
{
ds.ReadXmlSchema(stream);
}
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
if (cn.State == ConnectionState.Closed)
cn.Open();
if (exportMembershipData)
{
string[] aspnet_TableNames = new string[] { "aspnet_Applications", "aspnet_Membership", "aspnet_Profile", "aspnet_Roles", "aspnet_Users", "aspnet_UsersInRoles" };
using (SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_ExportMembership"), cn))
{
cmd.CommandType = CommandType.StoredProcedure;
using (IDataReader dr = cmd.ExecuteReader())
{
ds.Load(dr, LoadOption.OverwriteChanges, aspnet_TableNames);
}
}
}
if (exportGalleryData)
{
string[] gs_TableNames = new string[] { "gs_Album", "gs_Gallery", "gs_MediaObject", "gs_MediaObjectMetadata", "gs_Role", "gs_Role_Album", "gs_AppError", "gs_SchemaVersion" };
using (SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_ExportGalleryData"), cn))
{
cmd.CommandType = CommandType.StoredProcedure;
using (IDataReader dr = cmd.ExecuteReader())
{
ds.Load(dr, LoadOption.OverwriteChanges, gs_TableNames);
}
}
ds.WriteXml(filePath, XmlWriteMode.WriteSchema);
//ds.WriteXmlSchema(filePath);
}
}
}
protected DataSet.DS baseUpdate(DataSet.DS ds, string tableName)
{
_base.SetConnection();
_db = _base.GetDatabase();
ds.Load(_base.Update(ds), LoadOption.OverwriteChanges, tableName);
return ds;
}
/// <summary>
/// Excel导入Access
/// </summary>
public void Excel2Access(string[] filenames)
{
try
{
for (int j = 0; j < filenames.Length; j++)
{
OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
connectStringBuilder.DataSource = filenames[j];
connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
connectStringBuilder.Add("Extended Properties", "Excel 8.0");
using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
{
DataSet ds = new DataSet();
string sql = string.Format("select * from [{0}$]", sheetName);
OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
cn.Open();
using (OleDbDataReader dr = cmdLiming.ExecuteReader())
{
ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
DataTable dt = ds.Tables[sheetName];
if (dt.Rows.Count > 0)
{
for (int i = 4; i < dt.Rows.Count; i++)
{
//写入数据库数据
if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
{
string MySql = "insert into TB_Car (PlateNumber,Color,Brand,UseYear,MotorcyleType) values('" + dt.Rows[i][1].ToString() + "','" +
dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" +
dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "')";
AccessHelper.SQLExecute(MySql);
}
}
MessageBox.Show("数据导入成功！");
CarBLL carBLL = new CarBLL();
DataTable carList = carBLL.FindAll_infos(getblacklistsql);
dgvCarList.DataSource = carList;
}
else
{
MessageBox.Show("请检查你的Excel中是否存在数据");
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static DataSet DataSetFromSql(string sql, params string[] tableNames)
{
var dataSet = new DataSet();
using (var reader = DataReaderFromSql(sql))
dataSet.Load(reader, LoadOption.OverwriteChanges, tableNames);
return dataSet;
}
/// <summary>
/// Excel导入Access
/// </summary>
public void Excel2Access(string[] filenames)
{
try
{
for (int j = 0; j < filenames.Length; j++)
{
OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
connectStringBuilder.DataSource = filenames[j];
connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
connectStringBuilder.Add("Extended Properties", "Excel 8.0");
using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
{
DataSet ds = new DataSet();
string sql = string.Format("select * from [{0}$]", sheetName);
OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
cn.Open();
using (OleDbDataReader dr = cmdLiming.ExecuteReader())
{
ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
DataTable dt = ds.Tables[sheetName];
if (dt.Rows.Count > 0)
{
for (int i = 4; i < dt.Rows.Count; i++)
{
//写入数据库数据
if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
{
string MySql = "insert into TB_BlacakList (User_ChineseName,User_EnglishName,Department_ChineseName,Department_EnglishName,Job,Sex,Birthdy,Identification_Type,Identification_Number,Employer,TELEPHONE) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
AccessHelper.SQLExecute(MySql);
}
}
MessageBox.Show("数据导入成功！");
string getblacklistsql = "select ID ,User_ChineseName as 中文,User_EnglishName as 英文,Department_ChineseName as 单位名称中文,Department_EnglishName as 单位名称英文,Job as 职务,Sex as 性别,Birthdy as 出生日期,Identification_Type as 身份证件类型,Identification_Number as 身份证件号码,Employer as 工作单位,TELEPHONE as 联系方式 from TB_BlacakList ";
DriverListBLL driverListBll = new DriverListBLL();
DataTable dtDriverList = driverListBll.FindAll_infos(getblacklistsql);
dgvBlackList.DataSource = dtDriverList;
dgvBlackList.Rows[0].Selected = false;
}
else
{
MessageBox.Show("请检查你的Excel中是否存在数据");
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
protected System.Data.DataSet baseGetList()
{
System.Data.DataSet ds = new System.Data.DataSet();
_base.SetConnection();
//_db = _base.GetDatabase();
ds.Tables.Add(_listTable);
ds.Load(_base.GetList(), LoadOption.OverwriteChanges, _listTable);
return ds;
}
///<summary>
/// Deserialize a protocol-buffer binary stream as a <see cref="System.Data.DataSet"/>.
///</summary>
///<param name="stream">The <see cref="System.IO.Stream"/> to read from.</param>
///<param name="tables">An array of strings, from which the <see cref="System.Data.DataSet"/> Load method retrieves table name information.</param>
public DataSet DeserializeDataSet(Stream stream, params string[] tables)
{
if (stream == null) throw new ArgumentNullException("stream");
if (tables == null) throw new ArgumentNullException("tables");
var dataSet = new DataSet();
using (var reader = Deserialize(stream))
dataSet.Load(reader, LoadOption.OverwriteChanges, tables);
return dataSet;
}
/// <summary>
/// Used to get the user
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static DataSet GetUser(string query)
{
DataSet dataset = new DataSet();
SqlConnection con = connection();
SqlCommand cmd = new SqlCommand(query, con);
cmd.CommandType = CommandType.Text;
var dr = cmd.ExecuteReader();
dataset.Load(dr, LoadOption.OverwriteChanges, "User");
return dataset;
}
public System.Data.DataSet GetEventList(string zipcode, int? distance,
ref decimal startLatitude, ref decimal startLongitude, ref bool is_Valid_Postal_Code, string start_Date, string end_Date)
{
DataAccessLayer.Parameter.ZipCodes obj2 = new DataAccessLayer.Parameter.ZipCodes();
obj2.SetConnection();
_db = obj2.GetDatabase();
obj2._zipcodes_ID = zipcode;
DataSet.DSParameter ds = new DataSet.DSParameter();
ds.Load(obj2.GetItem(), LoadOption.OverwriteChanges, ds.ZipCodes.TableName);
System.Data.DataSet __ds = new System.Data.DataSet();
if (ds.ZipCodes.Count == 0)
{
__ds.Tables.Add("List");
is_Valid_Postal_Code = false;
return __ds;
}
startLatitude = ds.ZipCodes[0].Latitude;
startLongitude = ds.ZipCodes[0].Longitude;
//_is_Single_Transaction = false;
//bool is_Valid = false;
try
{
DataAccessLayer.Search.Event obj = new DataAccessLayer.Search.Event();
obj.SetConnection();
_db = obj.GetDatabase();
IDataReader dr = obj.GetEventList(startLatitude, startLongitude, distance, start_Date, end_Date);
__ds.Load(dr, LoadOption.OverwriteChanges, "List");
}
catch
{
//_base.RollBackTransaction();
//throw;
}
is_Valid_Postal_Code = true;
return __ds;
}
public override void Run(object context)
{
DbController dbc = new DbController(Connection);
DataSet ds = new DataSet();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("SELECT * FROM {0} ", TableName.PRODUCT.ToString());
sql.AppendFormat("SELECT * FROM {0} ", TableName.CATEGORY.ToString());
sql.AppendFormat("SELECT * FROM {0} ", TableName.SUPPLIER.ToString());
DataTable[] tables = new DataTable[]{
new DataTable(TableName.PRODUCT.ToString()),
new DataTable(TableName.CATEGORY.ToString()),
new DataTable(TableName.SUPPLIER.ToString())};
try
{
ds.Tables.Add(tables[0]);
ds.Tables.Add(tables[1]);
ds.Tables.Add(tables[2]);
Command.CommandText = sql.ToString();
DataReader = Command.ExecuteReader();
ds.Load(DataReader, LoadOption.OverwriteChanges, tables);
if (ds.Tables[TableName.PRODUCT.ToString()].Rows.Count != dbc.GetProductCount())
Fail("DataSet failed to load all rows in Product table");
if (ds.Tables[TableName.CATEGORY.ToString()].Rows.Count != dbc.GetCategoryCount())
Fail("DataSet failed to load all rows in Category table");
if (ds.Tables[TableName.SUPPLIER.ToString()].Rows.Count != dbc.GetSupplierCount())
Fail("DataSet failed to load all rows in Supplier table");
}
catch (Exception e)
{
Fail(e);
}
finally
{
try
{
Command.Close();
}
catch (Exception e)
{
Fail(e);
}
base.Run(context);
}
}
public override DataTable GetInstance()
{
var connection = new SqlConnection(ConnectionString);
var command = new SqlCommand(string.Format("SELECT * FROM {0}", TableName), connection);
connection.Open();
var dataSet = new DataSet();
var table = dataSet.Tables.Add(TableName);
dataSet.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, new[] { table });
connection.Close();
table.PrimaryKey = table.Columns.Cast<DataColumn>().Where(currColumn => IdentityColumns.Contains(currColumn.ColumnName)).ToArray();
return table;
}
public static bool DatabaseIsValid(string connectionString, Verbosity verbosity, bool executeObjects)
{
bool rc = true;
int objectCount = 0;
int invalidObjectCount = 0;
using (SqlConnection db = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(GetProcedureListSql(db, verbosity), db))
{
if (db.State != ConnectionState.Open) db.Open();
using (DataSet procReader = new DataSet())
{
procReader.Locale = CultureInfo.CurrentCulture;
procReader.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "main");
bool succeeded = false;
//for each object get the command text and execute the command
foreach (DataRow dr in procReader.Tables[0].Rows)
{
string name = (string)dr["Name"];
string quotedIdent = (int)dr["quoted_ident_on"] == 1 ? "ON" : "OFF";
string ansiNulls = (int)dr["ansi_nulls_on"] == 1 ? "ON" : "OFF";
string schema = (string)dr["schema"];
if (verbosity == Verbosity.Verbose) Console.WriteLine("Processing {0}.{1}", schema, name);
string text = GetProcedureText(db, schema, name);
if (!string.IsNullOrEmpty(text))
{
succeeded = TryCompile(db, schema, name, quotedIdent, ansiNulls, text);
if (succeeded && executeObjects && dr["type"] as string != "TR")
succeeded = ExecuteIfSafe(db, schema, name, dr["type"] as string, text);
}
if (!succeeded && !string.IsNullOrEmpty(text)) invalidObjectCount++;
objectCount++;
}
if (verbosity != Verbosity.Quiet && verbosity != Verbosity.None)
Console.WriteLine("\nProcessing complete. Objects processed: {0}\tInvalid objects found: {1}", objectCount, invalidObjectCount);
}
db.Close();
}
if (invalidObjectCount > 0) rc = false;
return rc;
}
public static DataSet ExecuteQuery(string cmd, string connectstring, params SqlParameter[] parameters)
{
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectstring))
{
SqlCommand command = new SqlCommand(cmd, connection);
command.Parameters.AddRange(parameters);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
ds.Load(reader, LoadOption.OverwriteChanges, new string[] { "" });
reader.Close();
}
return ds;
}
public GridProductos( string sql )
{
InitializeComponent( );
InitConexionDB( );
// TODO: Complete member initialization
this.sqlQuery = sql;
CommonUtils.ConexionBD.AbrirConexion( );
gridControl.MainView = cardView;
SqlCommand cmdProduct = new SqlCommand( this.sqlQuery , CommonUtils.ConexionBD.Conexion );
DataSet dataSet = new DataSet( );
dataSet.Load( cmdProduct.ExecuteReader( ) , LoadOption.OverwriteChanges , "Producto" );
gridControl.DataSource = dataSet.Tables[ "Producto" ];
ColumnView view = this.gridControl.MainView as ColumnView;
view.Columns.Add( );
DevExpress.XtraEditors.Repository.RepositoryItemPictureEdit rPic = new DevExpress.XtraEditors.Repository.RepositoryItemPictureEdit( );
rPic.NullText = "No Image";
rPic.SizeMode = DevExpress.XtraEditors.Controls.PictureSizeMode.Squeeze;
view.Columns[ 1 ].ColumnEdit = rPic;
view.Columns[ 1 ].GetBestWidth( );
/*
* Make just the Default columns visible. Imagen and Precio de Venta.
*/
view.Columns[0].Visible = false;
view.Columns[2].Visible = false;
view.Columns[3].Visible = false;
view.Columns[4].Visible = false;
view.Columns[5].Visible = false;
view.Columns[6].Visible = false;
view.Columns[8].Visible = false;
/*
* The user can make them visible if needed.
*/
this.gridControl.RepositoryItems.Add( view.Columns[ 1 ].ColumnEdit );
CommonUtils.ConexionBD.CerrarConexion( );
}
/// <summary> Executes a SQL Stored Procedure with no parameters, and returns a DataSet.</summary>
/// <param name="StoredProcName">The actual name of the Stored Prcedure.</param>
/// <returns>DataSet</returns>
public DataSet SelectQuery(string StoredProcName)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
DataSet ds1 = new DataSet();
// create and open a connection object
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Home_ARC_ORGConnectionString"].ToString());
conn.Open();
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(StoredProcName, conn);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// execute the command
rdr = cmd.ExecuteReader();
ds1.Load(rdr, LoadOption.OverwriteChanges, "eNewLetter");
conn.Close();
return ds1;
}
public DataSet GetUserList(int pageIndex, int pageSize, string orderBy, Dictionary<string, object> parameters, out int total)
{
if(string.IsNullOrEmpty(orderBy))
{
orderBy="guid";
}
using (ISession session = sessionFactory.OpenSession())
{
IDbCommand cmd = session.Connection.CreateCommand();
string table = "select * from [user]";
string where = setWhereParameters(cmd, parameters);
string sql = SQLHelper.CreatPageSelectSQL(pageIndex, pageSize, table, where, orderBy);
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Connection = session.Connection;
DataSet dataset = new DataSet();
dataset.Load(cmd.ExecuteReader(), LoadOption.Upsert, new string[] { "User" });
string getCountSQL = SQLHelper.GetTotalCountSQL(table, where);
cmd.CommandText = getCountSQL;
total = (int)cmd.ExecuteScalar();
return dataset;
}
}
private void InitializeGrid( )
{
CommonUtils.ConexionBD.AbrirConexion( );
string sqlDosificacion = "SELECT dosificacion_llave AS [Llave], dosificacion_nro_autorizacion as [Nro de Autorizacion], dosificacion_ultimo_valor AS [Nro de Factura], " +
" dosificacion_fechalimite AS [Fecha Limite de Emision], dosificacion_activa AS [Activa], dosificacion_id " +
"From dosificacion ";
try
{
SqlCommand cmdDosificacion = new SqlCommand( sqlDosificacion , CommonUtils.ConexionBD.Conexion );
DataSet dataSet = new DataSet( );
dataSet.Load( cmdDosificacion.ExecuteReader( ) , LoadOption.OverwriteChanges , "Dosificacion" );
gridDosificacion.DataSource = dataSet.Tables[ "Dosificacion" ];
ColumnView view = this.gridDosificacion.MainView as ColumnView;
view.Columns.Add( );
view.Columns[ 5 ].Visible = false;
this.gridDosificacion.RepositoryItems.Add( view.Columns[ 1 ].ColumnEdit );
MessageBarValue = dataSet.Tables[ "Dosificacion" ].Rows.Count.ToString( ) + " Dosificacion";
}
catch ( Exception ex )
{
log.Error( ex.Message , ex );
MessageBarValue = "No se pudieron listar las dosificaciones. Hubo un error: " + ex.Message;
}
finally
{
barItem.Text = MessageBarValue;
}
CommonUtils.ConexionBD.CerrarConexion( );
}
/// <summary>
/// Envoie une requête
/// </summary>
/// <param name="strRequete">Requête à exécuter</param>
/// <param name="columnsToRetrieve">Champs à récupérer</param>
/// <returns>DataSet contenant les champs</returns>
public static DataSet query(String strRequete, params String[] columnsToRetrieve)
{
String strConn = String.Format("server={0}; user id={1}; password={2}; database={3}",
server,
dbuser,
dbpass,
database);
DataSet ds = new DataSet();
try
{
using (MySqlConnection conn = new MySqlConnection(strConn))
{
conn.Open();
MySqlCommand requete = new MySqlCommand();
requete.Connection = conn;
requete.CommandText = strRequete;
MySqlDataReader dr = requete.ExecuteReader();
ds.Load(dr, LoadOption.OverwriteChanges, columnsToRetrieve);
}
}
catch (Exception sarlon)
{ ds = null; }
return ds;
}
/// <summary>
/// This method is used to retrieve the current data stored for a Entity in the database. This is used by the runtime to
/// detect the current values for a row that is being skipped due to data errors.
/// </summary>
/// <param name="entities">Entities whose current server version is required</param>
/// <param name="connection">SqlConnection to use for reading from the database</param>
/// <param name="transaction">SqlTransaction to use when reading from the database</param>
/// <returns>Server version or null if entity doesnt exist in database.</returns>
internal List<IOfflineEntity> GetCurrentServerVersionForEntities(IEnumerable<IOfflineEntity> entities, SqlConnection connection, SqlTransaction transaction)
{
var serverVersions = new List<IOfflineEntity>();
foreach (IOfflineEntity entity in entities)
{
// Craft the command for retrieving the row.
SqlCommand command = new SqlCommand(_converter.GetSelectScriptForType(entity.GetType()), connection, transaction);
int index = 1;
// Set parameters
foreach (PropertyInfo pinfo in ReflectionUtility.GetPrimaryKeysPropertyInfoMapping(entity.GetType())
)
{
SqlParameter p = new SqlParameter("@p" + index, pinfo.GetValue(entity, null));
index++;
command.Parameters.Add(p);
}
DataSet ds = new DataSet();
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.HasRows)
{
ds.Load(reader, LoadOption.OverwriteChanges, new string[] {_converter.GetTableNameForType(entity.GetType())});
serverVersions.Add(_converter.ConvertDataSetToEntities(ds).First());
}
else
{
serverVersions.Add(null);
}
}
}
return serverVersions;
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The datatable schema to add.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
SqlConnection sqlConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (sqlConnection = new SqlConnection(connectionString))
{
// Open the connection.
sqlConnection.Open();
// Create the command and assign any parameters.
dbCommand = new SqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.SqlDataType, queryText), sqlConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (SqlParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
dataSet = new System.Data.DataSet();
dataSet.Tables.AddRange(tables);
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
sqlConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (sqlConnection != null)
{
sqlConnection.Close();
}
}
}
public void ReaderExecuted(DbCommand command,
DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
_stopwatch.Stop();
DataTable dataTable = null;
if (interceptionContext.OriginalException == null)
{
var dataSet = new DataSet { EnforceConstraints = false };
dataTable = new DataTable(tableName: Guid.NewGuid().ToString());
dataSet.Tables.Add(dataTable);
dataSet.Load(interceptionContext.OriginalResult, LoadOption.OverwriteChanges, dataTable);
interceptionContext.Result = dataTable.CreateDataReader();
}
var context = EFProfilerContextProvider.GetLoggedResult(command, interceptionContext, _stopwatch.ElapsedMilliseconds, dataTable);
_profiler.ReaderExecuted(command, context);
}
public static DataTable getMailTemplateByParentId(int parentId)
{
EmailTemplateInfo Info = new EmailTemplateInfo();
string sql = "Select email_templateid as id,subject,body,status,header,footer,version,date_format(createddate,'%d-%b-%Y-%T') as createddate,username from email_template e inner join users u on e.userid=u.userid where parentid = ?parentid";
MySqlDataReader dr = DAO.ExecuteReader(sql, new MySqlParameter("parentid", parentId));
DataSet ds = new DataSet();
ds.EnforceConstraints = false;
DataTable dt = new DataTable();
ds.Load(dr, LoadOption.PreserveChanges, new string[1]);
dt = ds.Tables[0];
dr.Close();
dr.Dispose();
return dt;
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The data tables names to return.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteClientQuery(ref System.Data.DataSet dataSet, string[] tables, string queryText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
OracleClient.OracleCommand sqlCommand = null;
OracleClient.OracleConnection connection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (connection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create the command and assign any parameters.
sqlCommand = new OracleClient.OracleCommand(queryText, connection);
sqlCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
sqlCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = sqlCommand.ExecuteReader())
{
dataSet = new System.Data.DataSet();
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
connection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(sqlCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}