private void txtSurNameFilter_TextChanged(object sender, EventArgs e)
{
if (txtSurNameFilter.Text != "")
{
connStr = @"Data Source = .; Initial Catalog = Kaizen;Integrated Security = true ";
sqlCustomer = @"SELECT* FROM Customer WHERE CustSName like '%" + txtSurNameFilter + "%'";
daCustomer = new SqlDataAdapter(sqlCustomer, connStr);
cmdBCustomer = new SqlCommandBuilder(daCustomer);
daCustomer.FillSchema(dsKaizen, SchemaType.Source, "Customer");
daCustomer.Fill(dsKaizen, "Customer");
dgvCustomers.DataSource = dsKaizen.Tables["Customer"];
dgvCustomers.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
dgvCustomers.Refresh();
}
}
//métodos
public DataTable Listar()
{
SqlDataAdapter daEnfermeiro;
DataTable dtEnfermeiro = new DataTable();
try
{
daEnfermeiro = new SqlDataAdapter("Select * FROM Enfermeiro", frmPrincipal.conexao);
daEnfermeiro.Fill(dtEnfermeiro);
daEnfermeiro.FillSchema(dtEnfermeiro, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dtEnfermeiro);
}
public DataSet GetFileTable(string fileName)
{
string selectCommandText = $"SELECT * FROM [dbo].[{fileName}];";
var file = new DataSet();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, conn))
{
var mappings = adapter.TableMappings.Add(fileName, fileName);
conn.Open();
adapter.FillSchema(file, SchemaType.Source);
adapter.Fill(file);
}
}
return(file);
}
private void modify_Click(object sender, EventArgs e)
{
DataTable dt = ds.Tables[tableName];
sda.FillSchema(dt, SchemaType.Mapped);
DataRow dr = dt.Rows.Find(this.idTextBox.Text.Trim());
dr["house"] = this.housetextBox.Text.Trim();
dr["place"] = this.placetextBox.Text.Trim();
dr["mpn"] = this.mpnTextBox.Text.Trim();
dr["number"] = this.numberTextBox.Text.Trim();
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(sda);
sda.Update(dt);
MessageBox.Show("修改成功！");
}
private void LoadTableSchema(string tableName, SqlConnection connection)
{
DataSet dataset = new DataSet();
string sql = "SELECT * FROM " + tableName + " WHERE 0=1";
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
adapter.FillSchema(dataset, SchemaType.Source);
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable table = reader.GetSchemaTable();
reader.Close();
foreach (DataRow row in table.Rows)
{
TableSchema.FieldsRow field = _tableSchema.Fields.NewFieldsRow();
field.TableName = tableName;
field.FieldName = (string)row["ColumnName"];
field.DBType = (string)row["DataTypeName"];
SqlDbType sqlDbType = (SqlDbType)row["ProviderType"];
field.SqlDbType = "SqlDbType." + (sqlDbType).ToString();
field.SystemType = row["DataType"].ToString(); // (string)row["DataType"];
field.CustomType = ObjectGenerator.ConvertSystemTypeToShortHand(field.SystemType);
int size = (int)row["ColumnSize"];
field.Size = (sqlDbType == SqlDbType.VarChar || sqlDbType == SqlDbType.NVarChar) && size > 8000 ? -1 : size;
field.Precision = (short)row["NumericPrecision"];
field.Scale = (short)row["NumericScale"];
field.IsIdentity = (bool)row["IsIdentity"];
field.IsAutoInc = (bool)row["IsAutoIncrement"];
field.IsReadOnly = (bool)row["IsReadOnly"];
field.AllowDBNull = (bool)row["AllowDBNull"];
field.IsNullable = field.AllowDBNull && field.CustomType != "string";
field.IsPrimaryKey = row.Table.Columns.Contains("IsPrimaryKey") ? (bool)row["IsPrimaryKey"] : false;
for (int i = 0; i < dataset.Tables[0].PrimaryKey.Length; i++)
{
if (field.FieldName == dataset.Tables[0].PrimaryKey[i].ColumnName)
{
field.IsPrimaryKey = true;
}
}
_tableSchema.Fields.AddFieldsRow(field);
}
}
public DataTable Listar()
{
SqlDataAdapter daAluno;
DataTable dtAluno = new DataTable();
try
{
daAluno = new SqlDataAdapter("SELECT * FROM TBALUNO", Form1.conexao);
daAluno.Fill(dtAluno);
daAluno.FillSchema(dtAluno, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dtAluno);
}
/*
* vendorTextBox
* prouductTextBox
* mbBriefTextBox
* vendormaterialNoTextBox
* mpnTextBox
* replaceMpnTextBox
* custommaterialNotextBox
* replace_custom_materialNoTextBox
* fruNoTextBox
* replace_fruNoTextBox
* mb_descripeTextBox
* vendor_pch_mpnTextBox
* pcb_brief_describeTextBox
* pcb_describeTextBox
* vendor_vga_mpnTextBox
* vga_brief_describeTextBox
* vga_describeTextBox
* vendor_cpu_mpnTextBox
* cpu_briefTextBox
* cpu_describeTextBox
* dpk_typeTextBox
* dpkpnTextBox
* warranty_periodTextBox
* custom_machine_typeTextBox
* whole_machine_numTextBox
* areaTextBox
* statusTextBox
* cpu_typeTextBox
* cpu_freqTextBox
* numTextBox
* ===>
* Id
* vendor
* product
* mb_brief
* vendormaterialNo
* mpn
* replace_mpn
* custommaterialNo
* replace_custom_materialNo
* fruNo
* replace_fruNo
* mb_descripe
* vendor_pch_mpn
* pcb_brief_describe
* pcb_describe
* vendor_vga_mpn
* vga_brief_describe
* vga_describe
* vendor_cpu_mpn
* cpu_brief
* cpu_describe
* dpk_type
* dpkpn
* warranty_period
* custom_machine_type
* whole_machine_num
* area
* status
* cpu_type
* cpu_freq
* eco
* eol
* adddate
* inputuser
* */
private void modify_Click(object sender, EventArgs e)
{
DataTable dt = ds.Tables[tableName];
sda.FillSchema(dt, SchemaType.Mapped);
DataRow dr = dt.Rows.Find(this.numTextBox.Text.Trim());
dr["vendor"] = this.vendorTextBox.Text.Trim();
dr["product"] = this.prouductTextBox.Text.Trim();
dr["mb_brief"] = this.mbBriefTextBox.Text.Trim();
dr["vendormaterialNo"] = this.vendormaterialNoTextBox.Text.Trim();
dr["mpn"] = this.mpnTextBox.Text.Trim();
dr["replace_mpn"] = this.replaceMpnTextBox.Text.Trim();
dr["custommaterialNo"] = this.custommaterialNotextBox.Text.Trim();
dr["replace_custom_materialNo"] = this.replace_custom_materialNoTextBox.Text.Trim();
dr["fruNo"] = this.fruNoTextBox.Text.Trim();
dr["replace_fruNo"] = this.replace_fruNoTextBox.Text.Trim();
dr["mb_descripe"] = this.mb_descripeTextBox.Text.Trim();
dr["vendor_pch_mpn"] = this.vendor_pch_mpnTextBox.Text.Trim();
dr["pcb_brief_describe"] = this.pcb_brief_describeTextBox.Text.Trim();
dr["pcb_describe"] = this.pcb_describeTextBox.Text.Trim();
dr["vendor_vga_mpn"] = this.vendor_vga_mpnTextBox.Text.Trim();
dr["vga_brief_describe"] = this.vga_brief_describeTextBox.Text.Trim();
dr["vga_describe"] = this.vga_describeTextBox.Text.Trim();
dr["vendor_cpu_mpn"] = this.vendor_cpu_mpnTextBox.Text.Trim();
dr["cpu_brief"] = this.cpu_briefTextBox.Text.Trim();
dr["cpu_describe"] = this.cpu_describeTextBox.Text.Trim();
dr["dpk_type"] = this.dpk_typeTextBox.Text.Trim();
dr["dpkpn"] = this.dpkpnTextBox.Text.Trim();
dr["warranty_period"] = this.warranty_periodTextBox.Text.Trim();
dr["custom_machine_type"] = this.custom_machine_typeTextBox.Text.Trim();
dr["whole_machine_num"] = this.whole_machine_numTextBox.Text.Trim();
dr["area"] = this.areaTextBox.Text.Trim();
dr["_status"] = this.statusTextBox.Text.Trim();
dr["cpu_type"] = this.cpu_typeTextBox.Text.Trim();
dr["cpu_freq"] = this.cpu_freqTextBox.Text.Trim();
dr["eco"] = this.ecoTextBox.Text.Trim();
dr["eol"] = this.eolTextBox.Text.Trim();
dr["adddate"] = this.addDateTextBox.Text.Trim();
dr["inputuser"] = this.inputUserTextBox.Text.Trim();
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(sda);
sda.Update(dt);
}
public DataTable Listar()
{
SqlDataAdapter daTime;
DataTable dtTime = new DataTable();
try
{
daTime = new SqlDataAdapter("SELECT * FROM TIME", frmMenuPrincipal.conexao);
daTime.Fill(dtTime);
daTime.FillSchema(dtTime, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dtTime);
}
public void DisplayEmployee()
{
Con = new SqlConnection(ConnectionString);
dataAdapter = new SqlDataAdapter("SELECT * FROM EMPLOYEE", Con);
dataAdapter.FillSchema(dataset, SchemaType.Source, "Employee");
dataAdapter.Fill(dataset, "Employee");
DataTable dt = dataset.Tables["Employee"];
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
Console.Write(row[col] + " ");
}
Console.WriteLine("\n----------------------------------");
}
}
//Métodos
public DataTable Listar()
{
SqlDataAdapter daVacina;
DataTable dtVacina = new DataTable();
try
{
daVacina = new SqlDataAdapter("Select * FROM Vacina", frmPrincipal.conexao);
daVacina.Fill(dtVacina);
daVacina.FillSchema(dtVacina, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dtVacina);
}
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]);
}
public DataTable GetSchema(SqlConnection connection, String tableName)
{
if ((connection == null) || (connection.State == ConnectionState.Closed))
{
i_lastError = "Connection is null";
return(null);
}
DataTable tst = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter("SELECT top 0 * FROM " + tableName, connection);
adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
tst = adp.FillSchema(tst, SchemaType.Source);
return(tst);
//return Select("select top 0 * from " + tableName);
}
public DataSet GetProduct(string tableName)
{
DataSet ProductData = new DataSet();
Query = string.Format("SELECT * FROM " + tableName);
SqlConnection conn = new SqlConnection(connectionstring);
using (conn)
{
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(Query, conn);
adapter.FillSchema(ProductData, SchemaType.Source, tableName);
adapter.Fill(ProductData, tableName);
conn.Close();
}
return ProductData;
}
public static DataTable BuildDataTable()
{
DataTable productsTable = new DataTable("Products");
using (SqlConnection connect = Helper.GetSqlConnection())
{
SqlCommand command = connect.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[Order Details_BuildData]";
connect.Open();
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.FillSchema(productsTable, SchemaType.Mapped);
}
}
return(productsTable);
}
public DataTable Listar()
{
SqlDataAdapter daTipoDespesa;
DataTable dtTipoDespesa = new DataTable();
try
{
daTipoDespesa = new SqlDataAdapter("SELECT * FROM TIPODESPESA", btnMax.conexao);
daTipoDespesa.Fill(dtTipoDespesa);
daTipoDespesa.FillSchema(dtTipoDespesa, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dtTipoDespesa);
}
public DataTable Listar()
{
SqlDataAdapter daCidade;
DataTable dtCidade = new DataTable();
try
{
daCidade = new SqlDataAdapter("SELECT * FROM TB_CIDADE", FRM_PRINCIPAL.conexao);
daCidade.Fill(dtCidade);
daCidade.FillSchema(dtCidade, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dtCidade);
}
private DataTable ExecuteDataTable(string sql)
{
using (SqlConnection conn = new SqlConnection(txtConnStr.Text))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//只获得表的架构信息（列信息）
cmd.CommandText = sql;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.FillSchema(ds, SchemaType.Source);//获得表信息必须要写
adapter.Fill(ds);
return(ds.Tables[0]);
}
}
}
private DataTable GetDataSource(string sql)
{
DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection(ferncon))
{
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.FillSchema(table, SchemaType.Source);
da.Fill(table);
connection.Close();
}
return(table);
}
/// <summary>
/// Gets a data set.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="timeOut">The time out in seconds.</param>
/// <param name="schemaOnly">if set to <c>true</c> [schema only].</param>
/// <returns></returns>
private static DataSet GetDataSet(string query, CommandType commandType, Dictionary <string, object> parameters, int?timeOut = null, bool schemaOnly = false)
{
string connectionString = GetConnectionString();
if (!string.IsNullOrWhiteSpace(connectionString))
{
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand sqlCommand = new SqlCommand(query, con))
{
if (timeOut.HasValue)
{
sqlCommand.CommandTimeout = timeOut.Value;
}
sqlCommand.CommandType = commandType;
if (parameters != null)
{
foreach (var parameter in parameters)
{
SqlParameter sqlParam = new SqlParameter();
sqlParam.ParameterName = parameter.Key.StartsWith("@") ? parameter.Key : "@" + parameter.Key;
sqlParam.Value = parameter.Value;
sqlCommand.Parameters.Add(sqlParam);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
DataSet dataSet = new DataSet("rockDs");
if (schemaOnly)
{
adapter.FillSchema(dataSet, SchemaType.Source);
}
else
{
adapter.Fill(dataSet);
}
return(dataSet);
}
}
}
return(null);
}
public DataTable FillSchema(string cmdText)
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = cmdText;
DataTable toReturn = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
// Use base class' connection object
cmdToExecute.Connection = _mainConnection;
try
{
if (_mainConnectionIsCreatedLocal)
{
// Open connection.
_mainConnection.Open();
}
else
{
if (_mainConnectionProvider.IsTransactionPending)
{
cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction;
}
}
// Execute query.
adapter.FillSchema(toReturn, SchemaType.Mapped);
return(toReturn);
}
catch (Exception ex)
{
// some error occured. Bubble it to caller and encapsulate Exception object
throw new Exception("Error occured.", ex);
}
finally
{
if (_mainConnectionIsCreatedLocal)
{
// Close connection.
_mainConnection.Close();
}
cmdToExecute.Dispose();
adapter.Dispose();
}
}
private void checkCustomerDataChanged(int customerId)
{
SqlConnection con = new SqlConnection("Data Source=luxefood.database.windows.net;Initial Catalog=LuxeFoods;User ID=Klees;Password=Johnny69;Connect Timeout=60;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
DataSet ds = new DataSet();
con.Open();
SqlCommand read = new SqlCommand($"SELECT * from [user] WHERE id='{customerId}'", con);
SqlDataAdapter adapt = new SqlDataAdapter(read);
adapt.FillSchema(ds, SchemaType.Source, "CustomerInfo");
adapt.Fill(ds, "CustomerInfo");
con.Close();
DataTable tblCustomerInfo = ds.Tables["CustomerInfo"];
var names = customerNameTextBox.Text.Split();
if (names.Length >= 2)
{
foreach (DataRow x in tblCustomerInfo.Rows)
{
if ((string)x["voornaam"] != names[0] || (string)x["achternaam"] != names[1] || (string)x["email"] != customerEmailTextBox.Text || (string)x["telefoonnummer"] != customerPhoneTextBox.Text)
{
AcceptChangeButton.Show();
}
else
{
AcceptChangeButton.Hide();
}
}
}
else if (names.Length >= 1)
{
foreach (DataRow x in tblCustomerInfo.Rows)
{
if ((string)x["voornaam"] != names[0] || (string)x["achternaam"] != names[0] || (string)x["email"] != customerEmailTextBox.Text || (string)x["telefoonnummer"] != customerPhoneTextBox.Text)
{
AcceptChangeButton.Show();
}
else
{
AcceptChangeButton.Hide();
}
}
}
}
public DataTable Listar()
{
SqlDataAdapter daJogo;
DataTable dtJogo = new DataTable();
try
{
daJogo = new SqlDataAdapter("SELECT * FROM JOGO", frmMenuPrincipal.conexao);
daJogo.Fill(dtJogo);
daJogo.FillSchema(dtJogo, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dtJogo);
}
private DataTable PopulateSchema(string csv_file_path)
{
DataTable csvData = new DataTable();
string tableName = Path.GetFileNameWithoutExtension(csv_file_path);
using (SqlDataAdapter adapter = new SqlDataAdapter(String.Format("SELECT TOP 0 * FROM {0}", tableName), SqlConnection))
{
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Source, tableName);
adapter.Fill(ds, tableName);
csvData = ds.Tables[0];
};
return(csvData);
}
//只用来执行查询结果比较少的sql
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
DataSet dataset = new DataSet();
SqlDataAdapter apdater = new SqlDataAdapter(cmd);
apdater.FillSchema(dataset, SchemaType.Source);//获得表信息必须要写
apdater.Fill(dataset);
return(dataset.Tables[0]);
}
}
}
//Métodos
public DataTable Listar()
{
SqlDataAdapter daCidade;
DataTable dtCidade = new DataTable();
try
{
daCidade = new SqlDataAdapter("Select * FROM Cidade", frmPrincipal.conexao);
daCidade.Fill(dtCidade);
daCidade.FillSchema(dtCidade, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dtCidade);
}
/// <summary>
/// Fill the DataSet with DataTables.
/// </summary>
/// <param name="objConnection">Connection instance.</param>
/// <param name="objDataSet">DataSet instance.</param>
/// <param name="strTableName">TableName.</param>
/// <returns>DataSet instance.</returns>
private static object FillDataSet(SqlConnection objConnection, DataSet objDataSet, string strTableName)
{
try
{
string strCommand = "Select * from " + strTableName;
SqlDataAdapter objEmpData = new SqlDataAdapter(strCommand, objConnection);
objEmpData.MissingSchemaAction = MissingSchemaAction.AddWithKey;
objEmpData.FillSchema(objDataSet, SchemaType.Source, strTableName);
return(objDataSet);
}
catch (Exception ex)
{
Console.WriteLine("Error in Creating a DataSourceView - FillDataSet. Error Message -> " + ex.Message);
return(null);
}
}
private static DataTable GetDataTable(string sql, bool schema = false)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.SelectCommand.CommandTimeout = 300;
DataTable dt = new DataTable();
if (schema)
{
adapter.FillSchema(dt, SchemaType.Source);
}
adapter.Fill(dt);
adapter.Dispose();
return(dt);
}
}
public DataTable Listar()
{
SqlDataAdapter da_tdesp;
DataTable dt_tdesp = new DataTable();
try
{
da_tdesp = new SqlDataAdapter("SELECT * FROM TIPODESPESA", frmPrincipal.conexao);
da_tdesp.Fill(dt_tdesp);
da_tdesp.FillSchema(dt_tdesp, SchemaType.Source);
}
catch (Exception ex)
{
throw ex;
}
return(dt_tdesp);
}
public void Fill(SqlCommand command, SqlConnection connection)
{
FixCommandParameters(command);
if (_table != null)
{
_table.Dispose();
}
_table = new DataTable();
command.Connection = connection;
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.FillSchema(_table, SchemaType.Source);
adapter.Fill(_table);
}
}
/// <summary>
/// 不用Sql语句更新数据到数据库
/// </summary>
/// <param name="conn"></param>
/// <param name="table"></param>
/// <param name="sql"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public static bool UpdateTable(DataTable table, string sql, params object[] parameter)
{
if (checkInter( ) == false)
{
return(false);
}
bool result = false;
using (SqlConnection conn = new SqlConnection(Connstr))
{
//if ( QuickOpen ( conn ,500 ) == false )
// return false;
DataSet ds = new DataSet( );
try
{
table.TableName = "R_PQZB";
ds.Tables.Add(table.Copy( ));
SqlDataAdapter sda = new SqlDataAdapter( );
sda.SelectCommand = new SqlCommand(sql, conn);
SqlCommandBuilder cd = new SqlCommandBuilder(sda);
if (conn.State != ConnectionState.Open)
{
conn.Open( );
}
sda.FillSchema(ds, SchemaType.Mapped);
sda.Fill(ds);
table = ds.Tables[0];
int row = sda.Update(table);
table.AcceptChanges( );
result = true;
}
catch (SqlException ex)
{
Utility.LogHelper.WriteLog("批量保存", ex);
throw new Exception(ex.Message + "\n\r" + ex.StackTrace);
}
finally
{
conn.Close( );
}
}
return(result);
}
