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