public static List<OrderDetail> OrderDetailList(int OrderID)
{
//Copied code from Nwind.cs VVVV
List<OrderDetail> OrdDetList = new List<OrderDetail>();
sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlDataAdapter da;
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(PROC_ORD_DETAILS, sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@OrderID", OrderID));
da = new SqlDataAdapter(cmd);
da.FillSchema(dt, SchemaType.Source);
da.Fill(dt);
sqlCon.Close();
//Copied code^^^^
foreach (DataRow row in dt.Rows)
{
//int OrderID = (int)row["OrderID"];
//string ProductName = (string)row["ProductName"];
int ProductID = (int)row["ProductID"];
decimal UnitPrice = (decimal)row["UnitPrice"];
short Quantity = (short)row["Quantity"];
float Discount = (float)row["Discount"];
OrderDetail OrD = new OrderDetail(OrderID,ProductID,UnitPrice,Quantity,Discount);
OrdDetList.Add(OrD);
}
/*put code here */
return OrdDetList;
}
public static bool CloneDataTable(ref DataTable ReturnTable, string TableName)
{
System.Data.SqlClient.SqlDataAdapter lo_Ada = new System.Data.SqlClient.SqlDataAdapter();
DataTable Return_DataTable = new DataTable();
if (!OpenConnection())
{
return(false);
}
try
{
System.Data.SqlClient.SqlCommand SqlCmd;
SqlCmd = new System.Data.SqlClient.SqlCommand("Select TOP 1 * from " + TableName, lo_Connection);
SqlCmd.Connection = lo_Connection;
lo_Ada.SelectCommand = SqlCmd;
lo_Ada.FillSchema(Return_DataTable, SchemaType.Source);
lo_Ada.Dispose();
lo_Ada = null;
ReturnTable = Return_DataTable;
return(true);
}
catch
{
return(false);
}
finally
{
lo_Connection.Close();
}
}
public static DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
IDbCommand cmd = ctx.GetCommand(query as IQueryable);
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
adapter.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd;
DataTable dt = new DataTable();
try
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
finally
{
cmd.Connection.Close();
}
return(dt);
}
private void connectToServer()
{
SqlConnection conn = Connection.getConnection();
try
{
conn.Open();
tradeDataAdapter = new SqlDataAdapter("SELECT * FROM tb_trade", conn);
saleDataAdapter = new SqlDataAdapter("SELECT * FROM tb_sale", conn);
vipDataAdapter = new SqlDataAdapter("SELECT * FROM tb_vip", conn);
//先获取所有商品信息填充到dataset去
SqlCommand cmd = new SqlCommand("SELECT * FROM view_commodity_cashier", conn);
SqlDataReader reader = cmd.ExecuteReader();
DataTable tb_commodity = new DataTable("commodity");
tb_commodity.Load(reader);
dataset.Tables.Add(tb_commodity);
reader.Close();
//获取tb_trade表
tradeDataAdapter.Fill(dataset, "trade");
//获取tb_sale表
saleDataAdapter.Fill(dataset, "sale");
//获取tb_vip表
vipDataAdapter.Fill(dataset, "vip");
StringBuilder builder = new StringBuilder();
//获取当前的购物表模式
//拼接select语句
builder.Append("SELECT ");
builder.Append("tb_commodity.s_code 条形码, ");
builder.Append("tb_commodity.s_name 商品名, ");
builder.Append("tb_commodity.s_price 商品单价, ");
builder.Append("tb_sale.sale_count 数量, ");
builder.Append("tb_sale.sale_amount 合计 ");
builder.Append("FROM tb_commodity,tb_sale");
SqlDataAdapter shoplistAdapter = new SqlDataAdapter(builder.ToString(), conn);
shoplistAdapter.FillSchema(dt, SchemaType.Mapped);
SqlCommandBuilder cmdBuilder1 = new SqlCommandBuilder(tradeDataAdapter);
SqlCommandBuilder cmdBuilder2 = new SqlCommandBuilder(saleDataAdapter);
SqlCommandBuilder cmdBuilder3 = new SqlCommandBuilder(vipDataAdapter);
SqlCommandBuilder cmdBuilder4 = new SqlCommandBuilder(shoplistAdapter);
}
catch (Exception e)
{
MessageBox.Show("连接至服务器失败，请检查连接！错误："+e.ToString(), "错误");
}
finally
{
conn.Close();
}
}
/// <summary>
/// Gets all customers in the database
/// </summary>
/// <returns>DataTable with all customers in database</returns>
private DataTable getAllCountries(string startsWith)
{
// Create and instantiate connection
using (customerManagementConnection = new SqlConnection())
{
// Initialize connection string from web.config
customerManagementConnection.ConnectionString =
ConfigurationManager.ConnectionStrings[
"CustomerManagementConnectionString"].ConnectionString;
// Open connection
customerManagementConnection.Open();
// Declare and instantiate data adapter
using (SqlDataAdapter customerManagementDataAdapter = new SqlDataAdapter())
{
// Declare and instantiate command objects
SqlCommand selectCommand = new SqlCommand(
"SELECT * FROM Countries WHERE Name LIKE '" + startsWith + "%'", customerManagementConnection);
// Assign command objects
customerManagementDataAdapter.SelectCommand = selectCommand;
// Declare and instantiate DataTable
countriesDataTable = new DataTable("Countries");
// Apply the full schema from the data source
customerManagementDataAdapter.FillSchema(countriesDataTable, SchemaType.Source);
customerManagementDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
customerManagementDataAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
// Populate Customers DataTable
customerManagementDataAdapter.Fill(countriesDataTable);
}
}
return countriesDataTable;
}
public DataTable Consultar(string query)
{
DataTable dt = new DataTable();
cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.CommandTimeout = 10;
try
{
cmd.Connection = getConnection();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.FillSchema(dt, SchemaType.Source);
da.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cnn.Close();
}
return dt;
}
static DataSet FillDataSet(string SQLquery)
{
string sConnectionString = common.getConnectionString();
SqlConnection objConn
= new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daAuthors
= new SqlDataAdapter(SQLquery, objConn);
DataSet ds = new DataSet("Area");
daAuthors.FillSchema(ds, SchemaType.Source, "AreaTable");
daAuthors.Fill(ds, "AreaTable");
/*DataTable tblAuthors;
tblAuthors = ds.Tables["AreaTable"];
foreach (DataRow drCurrent in tblAuthors.Rows)
{
Console.WriteLine("{0} {1}",
drCurrent["ID"].ToString(),
drCurrent["Name"].ToString());
}
Console.ReadLine();*/
return ds;
}
public DataTable getAllStudents()
{
SqlConnection conn = null;
DataTable table = null;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
using (conn = new SqlConnection(connStr))
{
try
{
conn.Open();
String query = "SelectProcedure1";
SqlCommand com = new SqlCommand(query, conn);
com.CommandType = CommandType.StoredProcedure;
sqlDataAdapter.SelectCommand = com;
DataSet dataSet = new DataSet();
sqlDataAdapter.FillSchema(dataSet, SchemaType.Source, "Registration");
sqlDataAdapter.Fill(dataSet, "Registration");
table = dataSet.Tables["Registration"];
return table;
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString(), "Sql Exception");
return null;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
}
/// <summary>
/// /************************************************************************************************
/// Populate CUSTOMER table
/// By Wesley D. Brown
/// Date 11/24/2007
/// Mod
/// **Description**
/// Functions:
/// **End Discription**
/// **Change Log**
/// **End Change Log**
/// ************************************************************************************************/
/// </summary>
/// <returns></returns>
public void BuildCustomerTable()
{
var currdate = new DateTime();
var dt = new DataTable();
var sqlConnect = new SqlConnection {ConnectionString = Sqlconn};
sqlConnect.Open();
var dc = new SqlCommand("select top 0 * from dbo.CUSTOMER", sqlConnect) {CommandType = CommandType.Text};
var da = new SqlDataAdapter(dc);
da.FillSchema(dt, SchemaType.Mapped);
sqlConnect.Close();
sqlConnect.Dispose();
var bulkCopy = new SqlBulkCopy(Sqlconn,SqlBulkCopyOptions.TableLock) {DestinationTableName = "dbo.CUSTOMER", BatchSize = 10000};
int i = NumWh;
int tid = (NumWh*10*3000) + 1;
while (i < MaxNumWh + 1)
{
for (int id = 1; id < 11; id++)
{
for (int cid = 1; cid < 3001; cid++)
{
DataRow dr = dt.NewRow();
dr["C_ID"] = "C_W" + i + "_D" + id + "_" + cid;
dr["C_D_ID"] = "D_W" + i + "_" + id;
dr["C_W_ID"] = "W_" + i;
dr["C_FIRST"] = RandomString(5, 16);
dr["C_MIDDLE"] = "oe";
dr["C_LAST"] = RandomString(8, 16);
dr["C_STREET_1"] = RandomString(10, 20);
dr["C_STREET_2"] = RandomString(10, 20);
dr["C_CITY"] = RandomString(10, 20);
dr["C_STATE"] = RandomString(2, 2);
dr["C_ZIP"] = RandZip();
dr["C_PHONE"] = RandomString(12, 12);
dr["C_SINCE"] = currdate.ToLongTimeString();
dr["C_CREDIT"] = "GC";
dr["C_CREDIT_LIM"] = 5000;
dr["C_DISCOUNT"] = .5;
dr["C_BALANCE"] = -10.00;
dr["C_YTD_PAYMENT"] = 10.00;
dr["C_PAYMENT_CNT"] = 1;
dr["C_DELIVERY_CNT"] = 0;
dr["C_DATA"] = RandomString(300, 500);
dr["SEQ_ID"] = tid;
dt.Rows.Add(dr);
}
}
bulkCopy.WriteToServer(dt);
dt.Clear();
i++;
}
return;
}
protected DataSet GetAndFillDataSet(SqlDataAdapter adapter)
{
DataSet dts = new DataSet(NewTableName);
adapter.FillSchema(dts, SchemaType.Source, NewTableName);
adapter.Fill(dts);
return dts;
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
dsPubs.Clear();
gros = new SqlDataAdapter("SELECT [Gross] FROM Payments Where [ID] LIKE '%" + id[i].id.ToString() + "%' AND [Year] LIKE '%" + DateTime.Now.Year.ToString() + "%'", Form1.Conn);
dsPubs = new DataSet("Pubs");
gros.FillSchema(dsPubs, SchemaType.Source, "Payments");
gros.Fill(dsPubs, "Payments");
dsPubs.Dispose();
}
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(tblProduct, System.Data.SchemaType.Source);
}
public static void setupProdAdapter1()
{
con1.ConnectionString = ConStr;
prodCmd1.Connection = con1;
prodCmd1.CommandType = System.Data.CommandType.Text;
prodCmd1.CommandText = "Select * from Shopping order by ProductID";
prodAdapter1 = new System.Data.SqlClient.SqlDataAdapter(prodCmd1);
prodAdapter1.FillSchema(tblProduct1, System.Data.SchemaType.Source);
}
/// <summary>
/// 执行sql返回dataable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private DataTable ExecuteDataTable(string sql)
{
DataSet ds = new DataSet();
try
{
switch (sqltype)
{
case 1:
using (SqlConnection conn = new SqlConnection(cononstr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
adapter.FillSchema(ds, SchemaType.Source);
adapter.Fill(ds);
}
}
}
break;
case 2:
using (MySqlConnection conn = new MySqlConnection(cononstr))
{
conn.Open();
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
{
adapter.FillSchema(ds, SchemaType.Source);
adapter.Fill(ds);
}
}
}
break;
default: break;
}
return ds.Tables[0];
}
catch (Exception ex)
{
this.Dispatcher.Invoke(delegate
{
tbModel.Text = "连接字符串填写错误，请检查。\n错误信息：" + ex.Message + "\n" + ex.ToString();
tbDAL.Text = ex.Message + "\n" + ex.ToString();
});
ConnectSqlFailed();
return null;
}
finally
{
this.Dispatcher.Invoke(delegate { btnConnect.IsEnabled = true; tbConnStr.IsEnabled = true; });
}
}
private void cargarTabla(string tabla)
{
SqlConnection conexion = conectarBD();
conexion.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(String.Format("SELECT * FROM {0}", tabla), conexion);
dataAdapter.FillSchema(Datos, SchemaType.Source, tabla);
dataAdapter.Fill(Datos, tabla);
dataAdapters.Add(tabla, dataAdapter);
conexion.Close();
}
private void log_Load(object sender, EventArgs e)
{
SqlDataAdapter daAuthors = new SqlDataAdapter("Select * From log", Form1.Conn);
DataSet dsPubs = new DataSet("Pubs");
//dsPubs.Tables["LTD"].Columns.Add(no);
daAuthors.FillSchema(dsPubs, SchemaType.Source, "log");
daAuthors.Fill(dsPubs, "log");
//dsPubs.Tables["LTD"].Columns.Add(no);
bindingSource1.DataSource = dsPubs.Tables["log"];
dgv_log.DataSource = bindingSource1;
}
public Table GetTableMetadata(string tableName)
{
string selectCmdText = string.Format("SELECT * FROM [{0}]", tableName);
;
SqlCommand command = new SqlCommand(selectCmdText, conn);
SqlDataAdapter ad = new SqlDataAdapter(command);
System.Data.DataSet ds = new DataSet();
ad.FillSchema(ds, SchemaType.Mapped, tableName);
Table table = new Table(ds.Tables[0]);
return table;
}
private DataTable Products()
{
var connection = ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString;
using (var dataAdapter = new SqlDataAdapter("SELECT * from Products", connection))
{
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dataAdapter.FillSchema(dataTable, SchemaType.Mapped);
return dataTable;
}
}
public static void setupEmpAdapter()
{
con.ConnectionString = ConStr;
empCmd.Connection = con;
empCmd.CommandType = System.Data.CommandType.Text;
empCmd.CommandText = "Select * from Employee order by EmpID";
empAdapter = new System.Data.SqlClient.SqlDataAdapter(empCmd);
empAdapter.FillSchema(tblEmployee, System.Data.SchemaType.Source);
}
public MyDBTable(SqlConnection conn, string sql)
{
this._conn = conn;
if (_conn.State != ConnectionState.Open) _conn.Open();
this._tableName = "_tft_";// tableName;
//string sql = "select * from [" + _tableName + "] ";
this._adapter = new SqlDataAdapter(sql, _conn);
_adapter.FillSchema(_ds, SchemaType.Source, _tableName);
_adapter.Fill(_ds, _tableName);
_dt = _ds.Tables[_tableName];
}
//-----------------------------------------------------------------------------
//Funcion: EjecProcedimientoAlmacenado
//Parámetro: dsActual (System.String)
//Parámetro: nombreProcedimiento (System.String)
//Parámetro: bTiparDataset (System.Boolean)
//Parámetro: nombreTabla (System.String)
//Retorno: (System.Data.DataSet)
//ejecuta un store procedure de sql server
//Fecha : 20/06/2007 - 'Usuario:
//-----------------------------------------------------------------------------
private void _EjecucionProcedimientoAlmacenado(ref DataSet dsActual, string nombreProcedimiento, bool bTiparDataset, string nombreTabla)
{
System.Data.SqlClient.SqlConnection oSqlConnection = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlDataAdapter oSqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand oSqlCommand = new System.Data.SqlClient.SqlCommand();
//Dim oDataset As New System.Data.DataSet
int nInd; //Contador para bucle insercion parametros
try
{
//Si de antemano, sabemos que no es posible la ejecución lanzamos el error.
if (this.CadenaConexion == String.Empty)
{
throw new Exception("Falta establecer la cadena de conexión");
}
oSqlCommand.Connection = oSqlConnection;
oSqlCommand.CommandType = CommandType.StoredProcedure;
oSqlCommand.CommandText = nombreProcedimiento;
if (this.nTiempoEjecucionConsulta != -1)
{
oSqlCommand.CommandTimeout = nTiempoEjecucionConsulta;
}
for (nInd = 0; nInd < this._NombreParametros.Count; nInd++)
{
oSqlCommand.Parameters.AddWithValue("@" + Convert.ToString(_NombreParametros[nInd]), Convert.ToString(_ValorParametros[nInd]));
}
oSqlConnection.ConnectionString = this.CadenaConexion;
oSqlConnection.Open();
oSqlDataAdapter.SelectCommand = oSqlCommand;
oSqlDataAdapter.Fill(dsActual, nombreTabla);
if (bTiparDataset)
{
oSqlDataAdapter.FillSchema(dsActual, SchemaType.Mapped);
}
}
finally
{
if (oSqlConnection != null)
{
if (oSqlConnection.State != ConnectionState.Closed)
{
oSqlConnection.Close();
}
oSqlConnection.Dispose();
}
}
}
public void DumpTypeNames()
{
using (var conn = DB.GetConn())
{
var adapter = new SqlDataAdapter("select * from AllTypes", conn);
var ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Source, "AllTypes");
var table = ds.Tables["AllTypes"];
foreach (DataColumn column in table.Columns)
{
Console.WriteLine(column.DataType.Name);
}
}
}
public void GenerateFieldSettingCode()
{
using (var conn = DB.GetConn())
{
var adapter = new SqlDataAdapter("select * from AllTypes", conn);
var ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Source, "AllTypes");
var table = ds.Tables["AllTypes"];
foreach (DataColumn column in table.Columns)
{
Console.WriteLine("row[\"{0}\"] = {1};", column.ColumnName, ColumnDataSerializer(column.DataType));
}
}
}
//Metodo para actualizar los usuarios
public static void ActualizarUsuarios()
{
//Cadena para hacer las consultas
string CADENA;
//Metodos de conexiones
string sConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
OdbcConnection conexSql = new OdbcConnection(sConnectionString);
//metodo para abrir las cadenas de conexiones
OdbcConnection objcon = new OdbcConnection(sConnectionString);
objcon.Open();
//Cadena de string
CADENA = "SELECT usuario.usuacodi, usuario.usuapass, emplead.emplnomb FROM emplead emplead, usuario usuario WHERE emplead.emplterc = usuario.usuaterc";
//DataAdapter que se llena
OdbcDataAdapter daUsuariofox = new OdbcDataAdapter(CADENA, objcon);
//creamos un dataset
DataSet dsUsuariofox = new DataSet();
//llenamos el esquema
daUsuariofox.FillSchema(dsUsuariofox, SchemaType.Source, "usuario_FOX");
daUsuariofox.Fill(dsUsuariofox, "usuario_FOX");
//creamos un datatable
DataTable tbUsuariofox = new DataTable();
tbUsuariofox = dsUsuariofox.Tables["usuario_FOX"];
//////////////////////// sql
string sConnectionStringsql;
sConnectionStringsql = ("Server=servidor2sp;Database= CONTROL_ALMACEN;User Id=sa;Password=Qwer1234;");
SqlConnection objConnsql = new SqlConnection(sConnectionStringsql);
objConnsql.Open();
SqlDataAdapter dausuariosql = new SqlDataAdapter("Select * From Usuarios", objConnsql);
DataSet dsusuariosql = new DataSet();
dausuariosql.FillSchema(dsusuariosql, SchemaType.Source, "usuario_sql");
dausuariosql.Fill(dsusuariosql, "usuario_sql");
DataTable dtusuariosql = new DataTable();
dtusuariosql = dsusuariosql.Tables["usuario_sql"];
/////////////
compararUsuarios(tbUsuariofox, dtusuariosql, objConnsql);
//cerramos la conexion
objConnsql.Close();
objcon.Close();
}
/// <summary>
/// Get Column info from Stored procedure result set
/// </summary>
/// <param name="schema"></param>
/// <param name="storedProcName"></param>
/// <returns></returns>
public List<DataColumn> GetColumnInfoFromStoredProcResult(string schema, string storedProcName)
{
//this one actually needs to use the dataset because it has the only accurate information about columns and if they can be null or not.
var sb = new StringBuilder();
if (!String.IsNullOrEmpty(schema))
{
sb.Append(String.Format("exec [{0}].[{1}] ", schema, storedProcName));
}
else
{
sb.Append(String.Format("exec [{0}] ", storedProcName));
}
var prms = GetStoredProcedureInputParameters(schema, storedProcName);
var count = 1;
foreach (var param in prms)
{
sb.Append(String.Format("{0}=null", param.Name));
if (count < prms.Count)
{
sb.Append(", ");
}
count++;
}
var ds = new DataSet();
using (var sqlConnection = (SqlConnection)databaseWrapper.GetOpenDbConnection())
{
using (var sqlAdapter = new SqlDataAdapter(sb.ToString(), sqlConnection))
{
if (sqlConnection.State != ConnectionState.Open) sqlConnection.Open();
sqlAdapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly);
sqlConnection.Close();
sqlAdapter.FillSchema(ds, SchemaType.Source, "MyTable");
}
}
var list = new List<DataColumn>();
if (ds.Tables.Count > 0)
{
list = ds.Tables["MyTable"].Columns.Cast<DataColumn>().ToList();
}
return list;
}
public DataTable GetProducts()
{
string connectionString = Properties.Settings.Default.Store;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetProducts", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
// Get read-only column info.
adapter.FillSchema(ds, SchemaType.Mapped, "Products");
// Fill the table.
adapter.Fill(ds, "Products");
return ds.Tables[0];
}
static DataSet FillDataSet(string SQLquery)
{
string sConnectionString = common.getConnectionString();
SqlConnection objConn
= new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daAuthors
= new SqlDataAdapter(SQLquery, objConn);
DataSet ds = new DataSet("Area");
daAuthors.FillSchema(ds, SchemaType.Source, "AreaTable");
daAuthors.Fill(ds, "AreaTable");
return ds;
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
adapter = new SqlDataAdapter(commandString, connectionString);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
ConfigureEmployeesAdapter(adapter);
adapter.FillSchema(employees, SchemaType.Mapped);
employees.Columns[0].AutoIncrementSeed = -1;
adapter.Fill(employees);
dtgrd.ItemsSource = employees.DefaultView;
adapter.RowUpdated += adapter_RowUpdated;
}
public static DataSet GetSchemaSqlServer(string connectstring, string tableName)
{
var sql = "select * from " + tableName + " where 1=0;";
var fbConnection = new SqlConnection {ConnectionString = connectstring};
var ds = new DataSet();
var adapter = new SqlDataAdapter(sql, fbConnection);
adapter.Fill(ds);
//For Get Lenght
var dataTables = adapter.FillSchema(ds, SchemaType.Source);
ds.Tables.Clear();
ds.Tables.Add(dataTables[0]);
return ds;
}
public static DataTable ReadData(string comm)
{
DataTable dt = new DataTable();
try
{
if (Cnn == null) Cnn = new SqlConnection(StrCnn);
SqlDataAdapter da = new SqlDataAdapter(comm, Cnn);
da.FillSchema(dt, SchemaType.Mapped);
da.Fill(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return dt;
}
protected void btnLogin_Click(object sender, EventArgs e)
{
try
{
//ConStr = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + @"'J:\Web Protocols\Prog3\UWPCS3870.mdf';" + "Integrated Security=True;Connect Timeout=30";
System.Data.SqlClient.SqlDataAdapter userDataAdapter;
System.Data.SqlClient.SqlCommand userDataCmd = new System.Data.SqlClient.SqlCommand();
System.Data.DataTable tblUserData = new System.Data.DataTable("UserData");
userDataCmd.Connection = conn;
userDataCmd.CommandType = System.Data.CommandType.Text;
userDataCmd.CommandText = "Select * from UserInfo order by userID";
userDataAdapter = new System.Data.SqlClient.SqlDataAdapter(userDataCmd);
userDataAdapter.FillSchema(tblUserData, System.Data.SchemaType.Source);
userDataAdapter.Fill(tblUserData);
string passwordInput = txtPassword.Text;
string userNameInput = txtUserName.Text;
for (int i = 0; i < tblUserData.Rows.Count; i++)
{
if (userNameInput == tblUserData.Rows[i][0].ToString().Trim() && passwordInput == tblUserData.Rows[i][3].ToString().Trim())
{
Application["CurrentUser"] = userNameInput;
Response.Write("Login Successful!");
if (tblUserData.Rows[i][4].ToString().Trim() == "Admin")
{
Response.Redirect(@"/Admin\Default.aspx");
}
else
{
Response.Redirect(@"/User\Default.aspx");
}
}
}
throw new Exception();
}
catch (Exception ex)
{
lblErrorMessage.Text = "Your login attempt was not successful. Please try again.";
lblErrorMessage.Visible = true;
}
}
public void Explore(string classList, string hierarchyName)
{
HierarchyExplorerConfiguration currentConfig = ExportConfiguration.HierarchyExplorerConfigs[hierarchyName];
this.UsedMacrosList = "";
currentConfig.ClassList = classList;
string sql = currentConfig.Hierarchy.AddRootWhereToSql(currentConfig.SQL);
SqlCommand sqlSelectCommand = new SqlCommand(sql, ExportConfiguration.SqlConnection);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlSelectCommand);
DataTable RootData = new DataTable();
Data = new DataTable();
sqlAdapter.Fill(RootData);
sqlAdapter.FillSchema(Data, SchemaType.Source);
Data.PrimaryKey = null;
foreach (DataColumn dc in Data.Columns)
{
if (dc.DataType == typeof(string))
dc.MaxLength = int.MaxValue;
if (dc.Unique)
dc.Unique = false;
}
AddSubRows(Data, RootData, 0, "", currentConfig, new List<string>());
if (currentConfig.MacroFieldProps != null)
{
string notUsedSql = "SELECT " + currentConfig.MacroFieldProps.IdField + " FROM " + currentConfig.MacroFieldProps.TableName +
" WHERE " + currentConfig.MacroFieldProps.IdField + " NOT IN (" + UsedMacrosList + ")";
SqlCommand notUsedCommand = new SqlCommand(notUsedSql, ExportConfiguration.SqlConnection);
SqlDataAdapter notUsedAdapter = new SqlDataAdapter(notUsedCommand);
DataTable notUsedDt = new DataTable();
notUsedAdapter.Fill(notUsedDt);
NotUsedMacrosList = "";
bool first = true;
foreach (DataRow dr in notUsedDt.Rows)
{
if (first)
first = false;
else
NotUsedMacrosList += ",";
NotUsedMacrosList += "'" + dr[0].ToString() + "'";
}
}
OnPropertyChanged("Data");
OnPropertyChanged("UsedMacrosList");
OnPropertyChanged("NotUsedMacrosList");
}
public List<SearchResult> Search(string textToSearchFor)
{
var searchResults = new List<SearchResult>();
using (var conn = new SqlConnection(_databaseDataTableSet.ConnStr))
{
conn.Open();
foreach (var table in _databaseDataTableSet.TableList)
{
var fields = GetFieldsOfType(conn, table, SqlDbTypeGroup.String);
DataColumn[] keys;
using (var adapter = new SqlDataAdapter("select * from " + table, conn))
using (var dt = new DataTable(table))
{
adapter.FillSchema(dt, SchemaType.Mapped);
keys = dt.PrimaryKey;
}
foreach (DataRow fieldInfoRow in fields.Rows)
{
var fieldName = fieldInfoRow.Field<string>("COLUMN_NAME");
string fieldSearchSql = string.Format("select * from {0} where ([{1}] like '%{2}%')",
table, fieldName, textToSearchFor);
Log.Debug(string.Format("fieldSearchSql: {0}", fieldSearchSql));
var dataSearchResults = conn.ExecQuery(fieldSearchSql);
searchResults.AddRange(
dataSearchResults.Rows.Cast<DataRow>().Select(dataRow => new SearchResult()
{
TableName = table,
FieldName = fieldName,
FieldType = fieldInfoRow.Field<string>("DATA_TYPE"),
Sql = string.Format("{0} AND {1}", fieldSearchSql, MakeKeyFilter(keys,dataRow.KeyValues())),
Value = dataRow[fieldName],
KeyFields = keys,
KeyValue = dataRow.KeyValues()
}));
}
}
}
return searchResults;
}
public override void MigrateTable()
{
try
{
SQLConnection.Open();
AccessConnection.Open();
//Get Access Data
OleDbCommand oleCmd = AccessConnection.CreateCommand();
oleCmd.CommandText = "SELECT * FROM " + AccessTableName;
//get current records in SQL
SqlDataAdapter sqlAdapter = new SqlDataAdapter("SELECT * FROM COUNTRY", SQLConnection);
DataSet sqlCountry = new DataSet("Country");
sqlAdapter.FillSchema(sqlCountry, SchemaType.Source, "COUNTRY");
sqlAdapter.Fill(sqlCountry);
DataTable dt = sqlCountry.Tables["COUNTRY"];
var reader = oleCmd.ExecuteReader();
while (reader.Read())
{
var results = dt.Select("Name = '{0}'".Formatted(reader["COUNTRY"]));
if (results.Length == 0)
{
var newRow = dt.NewRow();
newRow["Name"] = reader["COUNTRY"];
dt.Rows.Add(newRow);
}
}
reader.Close();
dt.AcceptChanges();
}
catch (Exception ex)
{
throw ex;
}
finally
{
AccessConnection.Close();
SQLConnection.Close();//should we open and close for each database?
}
}
public object GetTownsFromSql(string townName)
{
if (townName == null) return DBNull.Value;
if (_towns == null)
{
SqlDataAdapter sqlAdapter = new SqlDataAdapter("SELECT * FROM Town", SQLConnection);
SqlCommandBuilder builder = new SqlCommandBuilder(sqlAdapter);
DataSet dts = new DataSet("Town");
sqlAdapter.FillSchema(dts, SchemaType.Source, "Town");
sqlAdapter.Fill(dts.Tables["Town"]);
_towns = dts.Tables["Town"];
}
var result = _towns.Select("Name = '{0}'".Formatted(townName));
if (result.Length == 0) return DBNull.Value;
else return result[0]["Id"];
}
// Загрузка в DataSet
public static void BuildDataSet(ref SqlConnection connect, ref DataSet ds, ref SqlDataAdapter[] adapt)
{
SqlCommand[] commands = new SqlCommand[3];
commands[0] = new SqlCommand("SELECT * from Tovar", connect);
daTovar = new SqlDataAdapter(commands[0]);
daTovar.FillSchema(ds, SchemaType.Source, "Tovar");
daTovar.Fill(ds, "Tovar");
adapt[0] = daTovar;
commands[1] = new SqlCommand("SELECT * from Pokupatel", connect);
daPokupatel = new SqlDataAdapter(commands[1]);
daPokupatel.FillSchema(ds, SchemaType.Source, "Pokupatel");
daPokupatel.Fill(ds, "Pokupatel");
adapt[1] = daPokupatel;
commands[2] = new SqlCommand("SELECT * from Zakaz", connect);
daZakaz = new SqlDataAdapter(commands[2]);
daZakaz.FillSchema(ds, SchemaType.Source, "Zakaz");
daZakaz.Fill(ds, "Zakaz");
adapt[2] = daZakaz;
}
private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (!e.Cancelled && !cancel)
{
if (e.Result != null)
{
Conn = (SqlConnection)e.Result;
if (Convert.ToBoolean(G_arg[5]))
{
progressBar1.ForeColor = Color.Aqua;
progressBar1.Style = ProgressBarStyle.Continuous;
progressBar1.Value = 100;
label_connect.Text = "Connected!";
SqlDataAdapter daAuthors = new SqlDataAdapter("Select TOP 100 * From LTD", Conn);
DataSet dsPubs = new DataSet("Pubs");
daAuthors.FillSchema(dsPubs, SchemaType.Source, "LTD");
daAuthors.Fill(dsPubs, "LTD");
bindingSource1.DataSource = dsPubs.Tables["LTD"];
dataGridView1.DataSource = bindingSource1;
Conn.Close();
}
else
{
this.Close();
}
}
else
{
this.Visible = false;
MessageBox.Show("Invalid Connection");
error = true;
this.Close();
}
}
}
//-----------------------------------------------------------------------------
//Funcion: EjecucionConsulta
//Parámetro: bTiparDataset (System.Boolean)
//Retorno: (System.Data.DataSet)
//Descripcion: Ejecuta la consulta a partir de la consultaSQL , cadena de conexión que se le haya pasado.
// Si se le pone bTiparDataset, el dataset resultante tiene información del Esquema
//-----------------------------------------------------------------------------
//public System.Data.DataSet EjecucionConsulta(bool bTiparDataset)
//{
// DataSet dsActual = new DataSet();
// this.EjecucionConsulta(ref dsActual, bTiparDataset, String.Empty, null);
// return dsActual;
//}
//-----------------------------------------------------------------------------
//Funcion: EjecucionConsulta
//Parámetro: dsActual (System.Data.DataSet)
//Parámetro: bTiparDataset (System.Boolean)
//Descripcion: Ejecuta la consulta a partir de la consultaSQL , cadena de conexión que se le haya pasado.
// Si se le pone bTiparDataset, el dataset resultante tiene información del Esquema
// Rellena los datos resultantes en el dataset pasado
//-----------------------------------------------------------------------------
//public void EjecucionConsulta(ref DataSet dsActual, bool bTiparDataset)
//{
// this._EjecucionConsulta(ref dsActual, bTiparDataset, String.Empty, null, CommandType.Text);
//}
//-----------------------------------------------------------------------------
//Funcion: EjecucionConsulta
//Parámetro: dsActual (System.Data.DataSet)
//Parámetro: bTiparDataset (System.Boolean)
//Parámetro: NombreTablaDestino (System.String)
//Descripcion: Ejecuta la consulta a partir de la consultaSQL , cadena de conexión que se le haya pasado.
// Si se le pone bTiparDataset, el dataset resultante tiene información del Esquema
// Rellena los datos resultantes en el dataset pasado
// Asigna al dataset pasado el nombre de tabla tambien pasado
//-----------------------------------------------------------------------------
//public void EjecucionConsulta(ref DataSet dsActual, bool bTiparDataset, string NombreTablaDestino)
//{
// this._EjecucionConsulta(ref dsActual, bTiparDataset, NombreTablaDestino, null, CommandType.Text);
//}
//-----------------------------------------------------------------------------
//Funcion: EjecucionConsulta
//Parámetro: dsActual (System.Data.DataSet)
//Parámetro: bTiparDataset (System.Boolean)
//Parámetro: NombreTablaDestino (System.String)
//Parámetro: oTransaccion (System.Data.IDbTransaction)
//Descripcion: Ejecuta la consulta a partir de la consultaSQL , cadena de conexión que se le haya pasado.
// Si se le pone bTiparDataset, el dataset resultante tiene información del Esquema
// Rellena los datos resultantes en el dataset pasado
// Asigna al dataset pasado el nombre de tabla tambien pasado
// Si se le pasa la transacción, se obtiene la conexion del otransaccion
//-----------------------------------------------------------------------------
//public void EjecucionConsulta(ref DataSet dsActual, bool bTiparDataset, string NombreTablaDestino, System.Data.IDbTransaction oTransaccion)
//{
// this._EjecucionConsulta(ref dsActual, bTiparDataset, NombreTablaDestino, oTransaccion, CommandType.Text);
//}
//-----------------------------------------------------------------------------
//Funcion: EjecucionConsulta
//Parámetro: dsActual (System.Data.DataSet)
//Parámetro: bTiparDataset (System.Boolean)
//Parámetro: NombreTablaDestino (System.String)
//Parámetro: oTransaccion (System.Data.IDbTransaction)
//Descripcion: Ejecuta la consulta a partir de la consultaSQL , cadena de conexión que se le haya pasado.
// Si se le pone bTiparDataset, el dataset resultante tiene información del Esquema
// Rellena los datos resultantes en el dataset pasado
// Asigna al dataset pasado el nombre de tabla tambien pasado
// Si se le pasa la transacción, se obtiene la conexion del otransaccion
//-----------------------------------------------------------------------------
//private void _EjecucionConsulta(ref DataSet dsActual, bool bTiparDataset, string NombreTablaDestino, System.Data.IDbTransaction oTransaccion, System.Data.CommandType Tipoconsulta)
//{
// System.Data.SqlClient.SqlConnection oSqlConnection;
// System.Data.SqlClient.SqlDataAdapter oSqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
// System.Data.SqlClient.SqlCommand oSqlCommand = new System.Data.SqlClient.SqlCommand();
// bool bTransaccionCreadaEnLaFuncion = true;
// // si se le pasa una transaccion, le asignamos la conexion
// if (oTransaccion != null)
// {
// bTransaccionCreadaEnLaFuncion = false;
// oSqlConnection = ((System.Data.SqlClient.SqlConnection)oTransaccion.Connection);
// }
// else
// {
// oSqlConnection = new System.Data.SqlClient.SqlConnection();
// }
// try
// {
// // Si de antemano, sabemos que no es posible la ejecución lanzamos el error.
// if (this.CadenaConexion == String.Empty)
// {
// throw new Exception("Falta establecer la cadena de conexión");
// }
// if (this.ConsultaSQL == String.Empty)
// {
// throw new Exception("Falta establecer la cadena de consulta");
// }
// oSqlCommand.Connection = oSqlConnection;
// oSqlCommand.CommandType = Tipoconsulta;
// oSqlCommand.CommandText = this.ConsultaSQL;
// if (this.nTiempoEjecucionConsulta != -1)
// {
// oSqlCommand.CommandTimeout = nTiempoEjecucionConsulta;
// }
// for (int nInd = 0; nInd < this._NombreParametros.Count; nInd++)
// {
// // oSqlCommand.Parameters.Add("@" + Convert.ToString(_NombreParametros(nInd)), Convert.ToString(_ValorParametros(nInd)));
// oSqlCommand.Parameters.AddWithValue("@" + Convert.ToString(_NombreParametros[nInd]), Convert.ToString(_ValorParametros[nInd]));
// }
// // si no tiene transaccion, asignamos la cadena de conexion y la abrimos
// if (oTransaccion == null)
// {
// oSqlConnection.ConnectionString = this.CadenaConexion;
// oSqlConnection.Open();
// oTransaccion = oSqlConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
// oSqlCommand.Transaction = ((System.Data.SqlClient.SqlTransaction)oTransaccion);
// }
// else
// {
// oSqlCommand.Transaction = ((System.Data.SqlClient.SqlTransaction)oTransaccion);
// }
// oSqlDataAdapter.SelectCommand = oSqlCommand;
// if ((NombreTablaDestino != null && NombreTablaDestino != String.Empty))
// {
// if (NombreTablaDestino.IndexOf(";") > -1)
// { //si hay mas de una tabla, hay que hacer un mapeo de ellas
// string[] tablas;
// tablas = Split(NombreTablaDestino, ";");
// int i = 0;
// oSqlDataAdapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping() { new System.Data.Common.DataTableMapping("Table", dsActual.Tables[0].TableName, new System.Data.Common.DataColumnMapping(-1) { }) });
// for (i = 1; i < tablas.Length; i++)
// {
// oSqlCommand.Parameters.AddWithValue("@" + Convert.ToString(_NombreParametros[nInd]), Convert.ToString(_ValorParametros[nInd]));
// }
// oSqlDataAdapter.Fill(dsActual);
// }
// else
// {
// oSqlDataAdapter.Fill(dsActual, NombreTablaDestino);
// }
// }
// else
// {
// oSqlDataAdapter.Fill(dsActual);
// }
// if (bTiparDataset)
// {
// oSqlDataAdapter.FillSchema(dsActual, SchemaType.Mapped);
// }
// }
// catch (Exception ex)
// {
// if (bTransaccionCreadaEnLaFuncion)
// {
// oTransaccion.Rollback();
// }
// }
// finally
// {
// // si la transaccion es nula, se cierra la conexion
// if (oTransaccion == null || bTransaccionCreadaEnLaFuncion)
// {
// if (oSqlConnection != null)
// {
// if (oTransaccion != null && oTransaccion.Connection !== null)
// {
// oTransaccion.Commit();
// oTransaccion.Dispose();
// }
// if (oSqlConnection.State != ConnectionState.Closed)
// {
// oSqlConnection.Close();
// }
// oSqlConnection.Dispose();
// }
// }
// }
//}
#endregion
// TODO: saber donde se utiliza!
//public System.Data.DataSet EjecucionConsulta(bool bTiparDataset, int filaOrigen, int nLineas, string nombreTabla)
public System.Data.DataSet EjecucionConsulta(bool bTiparDataset, string nombreTabla)
{
System.Data.SqlClient.SqlConnection oSqlConnection = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlDataAdapter oSqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand oSqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.DataSet oDataset = new System.Data.DataSet();
int nInd; //Contador para bucle insercion parametros
try
{
//Si de antemano, sabemos que no es posible la ejecución lanzamos el error.
if (this.CadenaConexion == String.Empty)
{
throw new Exception("Falta establecer la cadena de conexión");
}
if (this.ConsultaSQL == String.Empty)
{
throw new Exception("Falta establecer la cadena de consulta");
}
oSqlCommand.Connection = oSqlConnection;
oSqlCommand.CommandType = CommandType.Text;
oSqlCommand.CommandText = this.ConsultaSQL;
if (this.nTiempoEjecucionConsulta != -1)
{
oSqlCommand.CommandTimeout = nTiempoEjecucionConsulta;
}
for (nInd = 0; nInd < this._NombreParametros.Count; nInd++)
{
// oSqlCommand.Parameters.Add("@" + Convert.ToString(_NombreParametros(nInd)), Convert.ToString(_ValorParametros(nInd)));
oSqlCommand.Parameters.AddWithValue("@" + Convert.ToString(_NombreParametros[nInd]), Convert.ToString(_ValorParametros[nInd]));
}
oSqlConnection.ConnectionString = this.CadenaConexion;
oSqlConnection.Open();
oSqlDataAdapter.SelectCommand = oSqlCommand;
//oSqlDataAdapter.Fill(oDataset, filaOrigen, nLineas, nombreTabla);
oSqlDataAdapter.Fill(oDataset, nombreTabla);
if (bTiparDataset)
{
oSqlDataAdapter.FillSchema(oDataset, SchemaType.Mapped);
}
return(oDataset);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oSqlConnection != null)
{
if (oSqlConnection.State != ConnectionState.Closed)
{
oSqlConnection.Close();
}
oSqlConnection.Dispose();
}
}
}
