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