public DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query) { if (query == null) { throw new ArgumentNullException("query"); } IDbCommand cmd = ctx.GetCommand((IQueryable)query); System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd; DataTable dt = new DataTable("dataTbl"); cmd.Connection.ConnectionString = DynamicConnection.connectstr; try { cmd.Connection.Open(); adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } finally { adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); cmd.Connection.Close(); } return(dt); }
private void FrmAutores_Load(object sender, EventArgs e) { //trabaja en forma desconectada System.Data.SqlClient.SqlConnection _cnn = new System.Data.SqlClient.SqlConnection("Data Source= .; Initial Catalog = Biblioteca; Integrated Security = true"); try { //obtengo info a desplegar _DAautor = new System.Data.SqlClient.SqlDataAdapter("Exec ListoAutor", _cnn); _DS = new DataSet(); _DAautor.FillSchema(_DS, SchemaType.Source, "Autor"); _DAautor.Fill(_DS, "Autor"); //muestro informacion en pantalla DGVAutores.DataSource = _DS.Tables["Autor"]; //manipulo desde codigo el despliegue de la grilla DGVAutores.Columns[0].Visible = false; DGVAutores.Columns[1].HeaderText = "Nombre"; DGVAutores.Columns[2].HeaderText = "Nacionalidad"; } catch (Exception ex) { LblError.Text = ex.Message; } }
public static System.Data.DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query) { if (query == null) { throw new System.ArgumentNullException("query"); } System.Data.IDbCommand cmd = ctx.GetCommand(query as System.Linq.IQueryable); var adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd; var dt = new System.Data.DataTable(); try { cmd.Connection.Open(); adapter.FillSchema(dt, System.Data.SchemaType.Source); adapter.Fill(dt); } catch { throw; } finally { cmd.Connection.Close(); } return(dt); }
private void lLenarDesdeUnSpOTablaToolStripMenuItem_Click(object sender, EventArgs e) { Formularios.FormBuscarTabla dlg = new FormBuscarTabla(DB, Visor_sql_2015.Controladores_DB.TIPOOBJETO.STOREPROCERURE); if (dlg.ShowDialog() == DialogResult.Cancel) { return; } Objetos.CSysObject obj = dlg.DameObjeto(); int j, k; System.Data.SqlClient.SqlCommand SqlCommand1; SqlCommand1 = new System.Data.SqlClient.SqlCommand(); SqlCommand1.CommandText = obj.Nombre; SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure; SqlCommand1.Connection = new System.Data.SqlClient.SqlConnection(DB.ConnectionString); SqlCommand1.Connection.Open(); System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(SqlCommand1); System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1; sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter(); sqlDataAdapter1.SelectCommand = SqlCommand1; DataSet ds = new DataSet("aaa"); try { sqlDataAdapter1.GetFillParameters(); sqlDataAdapter1.FillSchema(ds, SchemaType.Source, "aaa"); k = ds.Tables.Count; for (j = 0; j < k; j++) { System.Data.DataTable t = ds.Tables[j]; int i, n; n = t.Columns.Count; Objetos.CConvertidor cv = new Objetos.CConvertidor(); for (i = 0; i < n; i++) { System.Data.DataColumn c = t.Columns[i]; Objetos.CParametro parametro = new Objetos.CParametro(); parametro.tipo = c.Caption; parametro.nombre = c.Caption; parametro.Logitud = c.MaxLength; this.Campos.Add(parametro); ListaCampos.Items.Add(parametro.tipo); //string TipoNet = cv.DameTipo(c.DataType.ToString()); //OnDatoManual(c.Caption, TipoNet); } } } catch (Exception ex) { SqlCommand1.Connection.Close(); throw new Exception(ex.Message); } SqlCommand1.Connection.Close(); MuestraDataGrid(); }
public static void setupProdAdapter() { con.ConnectionString = ConStr; prodCmd.Connection = con; prodCmd.CommandType = System.Data.CommandType.Text; prodCmd.CommandText = "Select * from product order by ProductID"; prodAdapter = new System.Data.SqlClient.SqlDataAdapter(prodCmd); prodAdapter.FillSchema(product, System.Data.SchemaType.Source); }
public static void setupShoppingCartAdapter() { con.ConnectionString = ConStr; cartCmd.Connection = con; cartCmd.CommandType = System.Data.CommandType.Text; cartCmd.CommandText = "Select * from ShoppingCart order by ProductID"; cartAdapter = new System.Data.SqlClient.SqlDataAdapter(cartCmd); cartAdapter.FillSchema(tblShoppingCart, System.Data.SchemaType.Source); }
public static void setupUserDataAdapter() { con.ConnectionString = ConStr; userCmd.Connection = con; userCmd.CommandType = System.Data.CommandType.Text; userCmd.CommandText = "Select * from UserData order by userID"; userAdapter = new System.Data.SqlClient.SqlDataAdapter(userCmd); userAdapter.FillSchema(tblUserData, System.Data.SchemaType.Source); }
public static void setupRoleAdapter() { con.ConnectionString = ConStr; roleCmd.Connection = con; roleCmd.CommandType = System.Data.CommandType.Text; roleCmd.CommandText = "Select * from RoleInfo order by RoleName"; roleAdapter = new System.Data.SqlClient.SqlDataAdapter(roleCmd); roleAdapter.FillSchema(tblRoleData, System.Data.SchemaType.Source); }
public static System.Data.DataSet GetInputDatasetShell(Activity dnActivity, IEnumerable <LinkedService> linkedServices, IEnumerable <Dataset> datasets) { //SQL or Azure Blob CSV only var inLS = LinkedServiceHelper.GetInputLinkedService(dnActivity, linkedServices, datasets); System.Data.DataSet dsRtn = new System.Data.DataSet(); //Figure out which Type switch (inLS.Properties.Type) { case "AzureStorage": foreach (var ds in dnActivity.Inputs) { var curTbl = dsRtn.Tables.Add(ds.Name); foreach (var d in datasets.First(ds1 => ds1.Name == ds.Name).Properties.Structure) { curTbl.Columns.Add(d.Name); } } break; case "AzureSqlDatabase": foreach (var ds in datasets) { var curTbl = dsRtn.Tables.Add(ds.Name); AzureSqlTableDataset astInput = ds.Properties.TypeProperties as AzureSqlTableDataset; System.Data.SqlClient.SqlConnection scInput = new System.Data.SqlClient.SqlConnection(((AzureSqlDatabaseLinkedService)inLS.Properties.TypeProperties).ConnectionString); System.Data.SqlClient.SqlCommand commInput = new System.Data.SqlClient.SqlCommand(); commInput.Connection = scInput; commInput.CommandType = System.Data.CommandType.Text; commInput.CommandText = string.Format("SELECT * FROM [{0}]", astInput.TableName); System.Data.SqlClient.SqlDataAdapter sdaInput = new System.Data.SqlClient.SqlDataAdapter(commInput); sdaInput.FillSchema(curTbl, System.Data.SchemaType.Source); } break; default: throw new NotImplementedException(); } return(dsRtn); }
private void MuestraCampos() { int j, k; System.Data.SqlClient.SqlCommand SqlCommand1; SqlCommand1 = new System.Data.SqlClient.SqlCommand(); SqlCommand1.CommandText = Nombre; SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure; SqlCommand1.Connection = new System.Data.SqlClient.SqlConnection(DB.ConnectionString); SqlCommand1.Connection.Open(); System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(SqlCommand1); System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1; sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter(); sqlDataAdapter1.SelectCommand = SqlCommand1; DataSet ds = new DataSet("aaa"); try { sqlDataAdapter1.GetFillParameters(); sqlDataAdapter1.FillSchema(ds, SchemaType.Source, "aaa"); k = ds.Tables.Count; for (j = 0; j < k; j++) { System.Data.DataTable t = ds.Tables[j]; int i, n; n = t.Columns.Count; Objetos.CConvertidor cv = new Objetos.CConvertidor(); for (i = 0; i < n; i++) { System.Data.DataColumn c = t.Columns[i]; string TipoNet = cv.DameTipo(c.DataType.ToString()); OnDatoManual(c.Caption, TipoNet); } } } catch (Exception ex) { SqlCommand1.Connection.Close(); throw new Exception(ex.Message); } SqlCommand1.Connection.Close(); }
/// <summary> /// Update one row /// </summary> /// <param name="pFileString">File String</param> /// <param name="pUpdateSQL">Update SQL Statement</param> /// <param name="pCommand">Command</param> /// <returns>bool</returns> public static bool Update_1_Row(string pFileString, string pSelectSQL, string pKeyString, System.Data.Common.DbDataReader pDataReader) { bool result = false; System.Data.SqlClient.SqlConnection Conn = null; System.Data.OleDb.OleDbConnection ConnOle = null; System.Data.SqlClient.SqlDataAdapter Adapter = null; System.Data.OleDb.OleDbDataAdapter AdapterOle = null; System.Data.SqlClient.SqlCommandBuilder builderSQL = null; System.Data.OleDb.OleDbCommandBuilder builderOLE = null; System.Data.SqlClient.SqlCommand cmdSqL = null; System.Data.Common.DbCommand cmdOle = null; DataSet dataSet = new DataSet(); DataTable Temp = new DataTable(); string ConnectionString = DBReadExecute.ParseConnectionString(pFileString); StringBuilder UpdateSQL = new StringBuilder(); try { Type SQLServerType = Type.GetType("Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer"); if (DBReadExecute.DataSource.GetType().AssemblyQualifiedName == SQLServerType.AssemblyQualifiedName) { //case DBReadExecute.enumDataSouce.SQLServer: Conn = new System.Data.SqlClient.SqlConnection(ConnectionString); Adapter = new System.Data.SqlClient.SqlDataAdapter(pSelectSQL, Conn); //Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName); Adapter.FillSchema(dataSet, SchemaType.Source); builderSQL = new System.Data.SqlClient.SqlCommandBuilder(Adapter); Conn.Open(); cmdSqL = Conn.CreateCommand(); cmdSqL = builderSQL.GetInsertCommand(); cmdSqL.CommandTimeout = 1500; UpdateSQL.Append("Update "); UpdateSQL.Append(pSelectSQL.Replace("Select * From ", "")); UpdateSQL.Append(" Set "); foreach (System.Data.SqlClient.SqlParameter param in cmdSqL.Parameters) { //string FieldName = param.ParameterName.TrimStart(new char[] { '@' }); string FieldName = param.SourceColumn; try { StringBuilder TUpdateSQL = new StringBuilder(); if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString())) { TUpdateSQL.Append("["); TUpdateSQL.Append(FieldName); TUpdateSQL.Append("]="); switch (pDataReader[FieldName].GetType().ToString()) { case "System.Boolean": if (Convert.ToBoolean(pDataReader[FieldName]) == false) { TUpdateSQL.Append("0"); } else { TUpdateSQL.Append("1"); } break; case "System.Int32": case "System.Decimal": case "System.Double": case "System.Single": case "System.Byte": TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''")); break; default: TUpdateSQL.Append("'"); TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''")); TUpdateSQL.Append("'"); break; } TUpdateSQL.Append(","); } UpdateSQL.Append(TUpdateSQL); } catch (Exception ex) { // do nothing } } UpdateSQL.Length = UpdateSQL.Length - 1; UpdateSQL.Append(" Where "); UpdateSQL.Append(pKeyString); //builderOLE = null; cmdSqL = null; cmdSqL = Conn.CreateCommand(); cmdSqL.CommandText = UpdateSQL.ToString(); cmdSqL.ExecuteNonQuery(); //break; } else { //case DBReadExecute.enumDataSouce.MSAccess: //case DBReadExecute.enumDataSouce.MSAccess2007: //case DBReadExecute.enumDataSouce.MSExcel: //case DBReadExecute.enumDataSouce.MSExcel2007: ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString); AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle); //Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName); AdapterOle.FillSchema(dataSet, SchemaType.Source); AdapterOle.Fill(Temp); builderOLE = new System.Data.OleDb.OleDbCommandBuilder(); builderOLE.DataAdapter = AdapterOle; ConnOle.Open(); cmdOle = ConnOle.CreateCommand(); cmdOle = builderOLE.GetInsertCommand(); cmdOle.CommandTimeout = 1500; UpdateSQL.Append("Update "); UpdateSQL.Append(pSelectSQL.Replace("Select * From ", "")); UpdateSQL.Append(" Set "); foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters) { //string FieldName = param.ParameterName.TrimStart(new char[] { '@' }); string FieldName = param.SourceColumn; try { StringBuilder TUpdateSQL = new StringBuilder(); if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString())) { TUpdateSQL.Append("["); TUpdateSQL.Append(FieldName); TUpdateSQL.Append("]="); switch (pDataReader[FieldName].GetType().ToString()) { case "System.Int32": case "System.Decimal": case "System.Boolean": case "System.Double": case "System.Single": case "System.Byte": TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''")); break; default: TUpdateSQL.Append("'"); TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''")); TUpdateSQL.Append("'"); break; } TUpdateSQL.Append(","); } UpdateSQL.Append(TUpdateSQL); } catch (Exception ex) { // do nothing } } UpdateSQL.Length = UpdateSQL.Length - 1; UpdateSQL.Append(" Where "); UpdateSQL.Append(pKeyString); builderOLE = null; cmdOle = null; cmdOle = ConnOle.CreateCommand(); cmdOle.CommandText = UpdateSQL.ToString(); //DBReadExecute.ExecuteSQL(pFileString, InsertSQL.ToString()); cmdOle.ExecuteNonQuery(); //break; } } catch (System.Exception ex) { Logger.Log(DateTime.Now + ": " + ex.Message); } finally { if (Conn != null) { Conn.Close(); } if (ConnOle != null) { ConnOle.Close(); } } result = true; return result; }
public static System.Data.DataSet GetOutputDatasetShell(Activity dnActivity, IEnumerable <LinkedService> linkedServices, IEnumerable <Dataset> datasets) { //SQL or Azure Blob CSV only var outLS = LinkedServiceHelper.GetOutputLinkedService(dnActivity, linkedServices, datasets); System.Data.DataSet dsRtn = new System.Data.DataSet(); //Figure out which Type switch (outLS.Properties.Type) { case "AzureStorage": foreach (var ds in dnActivity.Outputs) { System.Data.DataTable dtNew = new System.Data.DataTable(ds.Name); foreach (var d in datasets.Single(d => d.Name == ds.Name).Properties.Structure) { Type tCol = typeof(string); switch (d.Type) { case "Boolean": tCol = typeof(bool); break; case "Decimal": tCol = typeof(decimal); break; case "Enum": tCol = typeof(Enum); break; case "Guid": tCol = typeof(Guid); break; case "Int16": tCol = typeof(Int16); break; case "Int64": tCol = typeof(Int64); break; case "Int32": tCol = typeof(Int32); break; case "Datetime": tCol = typeof(DateTime); break; case "DateTimeOffset": tCol = typeof(DateTimeOffset); break; case "Double": tCol = typeof(double); break; case "Single": tCol = typeof(Single); break; case "Timespan": tCol = typeof(TimeSpan); break; } dtNew.Columns.Add(d.Name, tCol); } dsRtn.Tables.Add(dtNew); } break; case "AzureSqlDatabase": System.Data.SqlClient.SqlConnection scOutput = new System.Data.SqlClient.SqlConnection(((AzureSqlDatabaseLinkedService)outLS.Properties.TypeProperties).ConnectionString); System.Data.SqlClient.SqlCommand commOutput = new System.Data.SqlClient.SqlCommand(); foreach (var t in dnActivity.Outputs) { AzureSqlTableDataset astOutput = datasets.Single(d => d.Name == t.Name).Properties.TypeProperties as AzureSqlTableDataset; commOutput.Connection = scOutput; commOutput.CommandType = System.Data.CommandType.TableDirect; commOutput.CommandText = astOutput.TableName; System.Data.SqlClient.SqlDataAdapter sdaInput = new System.Data.SqlClient.SqlDataAdapter(commOutput); sdaInput.FillSchema(dsRtn, System.Data.SchemaType.Source); } break; default: throw new NotImplementedException(); } return(dsRtn); }