public static DataTable GetHandoverLogByHandoverLogId(int handoverLogId)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT WorkingTeamShiftLog.*,
StaffInfo.Name AS ChargeManName, ProductLine.Name AS ProductLineName,
WorkingTeam.Name AS WorkingTeamName
FROM WorkingTeam INNER JOIN
StaffInfo INNER JOIN
WorkingTeamShiftLog ON StaffInfo.ID = WorkingTeamShiftLog.ChargeManID INNER JOIN
ProductLine ON WorkingTeamShiftLog.ProductLineID = ProductLine.ID ON
WorkingTeam.ID = WorkingTeamShiftLog.WorkingTeamID
WHERE (WorkingTeamShiftLog.ID = @workingTeamShiftLogID)";
command.Parameters.Add(new SqlParameter("workingTeamShiftLogID", handoverLogId));
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(ds);
}
return ds.Tables[0];
}
public static void LerUsuarios_Desconectado()
{
DataSet ds = new DataSet();
using (var con = ObterConexao())
{
var cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM Usuario";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn column in table.Columns)
{
foreach (DataRow row in table.Rows)
{
Console.WriteLine("{0}: {1}", column.ColumnName, row[column.ColumnName]);
}
}
}
Console.Read();
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
SqlConnection con = new SqlConnection(constring);
con.Open();
int curRow = int.Parse(e.RowIndex.ToString());
string currentrow;
currentrow = dataGridView1.Rows[curRow].Cells[1].Value.ToString();
SqlDataAdapter adp1 = new SqlDataAdapter("Select * from ResManagerToAdmin where ResID='" + currentrow + "'", con);
DataSet ds1 = new DataSet();
adp1.Fill(ds1);
SqlCommand cmd = new SqlCommand("Update ResManagerToAdmin set RView='" + p2 + "' where ResID='" + currentrow + "'", con);
cmd.ExecuteNonQuery();
SqlCommand cmd1 = new SqlCommand("Update ResManagerToAdmin set RFinalView='" + p2 + "' where ResID='" + currentrow + "'", con);
cmd1.ExecuteNonQuery();
status = ds1.Tables[0].Rows[0]["RStatus"].ToString();
if (status == "Success")
{
ViewAdminResponses var = new ViewAdminResponses();
var.ShowDialog();
}
else
{
RecordNotFound rnf = new RecordNotFound();
rnf.ShowDialog();
}
}
public DataSet FilterData(ArsonFilterModel filterData)
{
var connectionString = ConfigurationManager.ConnectionStrings["UCR_DataEntities"];
DataSet dsResult = new DataSet();
using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
{
try
{
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = GenerateArsonORQuery(filterData);
command.CommandType = System.Data.CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
conn.Open();
adapter.Fill(dsResult);
conn.Close();
adapter.Dispose();
command.Dispose();
}
catch (Exception ex)
{
StorageClient.LogError(ex);
}
}
return dsResult;
}
public static string runDocno(string tablename, string fieldname, string doctype)
{
SqlConnection con = new SqlConnection(SysApp.DatabaseConfig.ServerConStr);
string sql = " select ";
sql += " case when max(substring(" + fieldname + ",4,6)) <> convert(varchar,getdate(),12) then ";
sql += " '" + doctype + "' + convert(varchar,getdate(),12) + '-0001' ";
sql += " when max(substring(" + fieldname + ",4,6)) = convert(varchar,getdate(),12) then ";
sql += " case ";
sql += " when (select max(substring(" + fieldname + ",11,4))+1 from " + tablename + " where substring(" + fieldname + ",4,6) = convert(varchar,getdate(),12) and " + fieldname + " like '" + doctype + "%') <10 then '" + doctype + "' + convert(varchar,getdate(),12) + '-000' + convert(varchar,(select max(substring(" + fieldname + ",11,4))+1 from " + tablename + " where substring(" + fieldname + ",4,6) = convert(varchar,getdate(),12) and " + fieldname + " like '" + doctype + "%')) ";
sql += " when (select max(substring(" + fieldname + ",11,4))+1 from " + tablename + " where substring(" + fieldname + ",4,6) = convert(varchar,getdate(),12) and " + fieldname + " like '" + doctype + "%') <100 then '" + doctype + "' + convert(varchar,getdate(),12) + '-00' + convert(varchar,(select max(substring(" + fieldname + ",11,4))+1 from " + tablename + " where substring(" + fieldname + ",4,6) = convert(varchar,getdate(),12) and " + fieldname + " like '" + doctype + "%')) ";
sql += " when (select max(substring(" + fieldname + ",11,4))+1 from " + tablename + " where substring(" + fieldname + ",4,6) = convert(varchar,getdate(),12) and " + fieldname + " like '" + doctype + "%') <1000 then '" + doctype + "' + convert(varchar,getdate(),12) + '-0' + convert(varchar,(select max(substring(" + fieldname + ",11,4))+1 from " + tablename + " where substring(" + fieldname + ",4,6) = convert(varchar,getdate(),12) and " + fieldname + " like '" + doctype + "%')) ";
sql += " else ";
sql += " '" + doctype + "' + convert(varchar,getdate(),12) + '-' + convert(varchar,(select max(substring(" + fieldname + ",11,4))+1 from " + tablename + " where substring(" + fieldname + ",4,6) = convert(varchar,getdate(),12) and " + fieldname + " like '" + doctype + "%')) ";
sql += " end ";
sql += " else ";
sql += " '" + doctype + "' + convert(varchar,getdate(),12) + '-0001' ";
sql += " end ";
sql += " as " + fieldname;
sql += " from " + tablename;
sql += " where " + fieldname + " like '" + doctype + "%' ";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds, "runDocno");
string docno = ds.Tables["runDocno"].Rows[0][fieldname].ToString();
da = null;
return docno;
}
public static DataTable Sehirler()
{
try
{
using (baglan = new SqlConnection(ConnectionString))
{
baglan.Open();
using (komutver = new SqlCommand())
{
komutver.Connection = baglan;
komutver.CommandText = "sehir_Listele";
komutver.CommandType = CommandType.StoredProcedure;
adp = new SqlDataAdapter(komutver);
dt = new DataTable();
adp.Fill(dt);
}
}
}
catch (Exception)
{
throw;
}
return dt;
}
public static DataTable Ilceler(int id)
{
try
{
using (baglan = new SqlConnection(ConnectionString))
{
baglan.Open();
using (komutver = new SqlCommand())
{
komutver.Connection = baglan;
komutver.CommandText = "ilce_Listele";
komutver.Parameters.Add("@ilceId", SqlDbType.Int);
komutver.Parameters["@ilceId"].Value = Convert.ToInt32(id);
komutver.CommandType = CommandType.StoredProcedure;
adp = new SqlDataAdapter(komutver);
dt = new DataTable();
adp.Fill(dt);
}
}
}
catch (Exception)
{
throw;
}
return dt;
}
public DataSet GetAll(string empresaid, tb_co_tablasunat BE)
{
using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
{
using (SqlCommand cmd = new SqlCommand("gspTbCoTablaSunat_CONSULTA", cnx))
{
DataSet ds = new DataSet();
{
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tablaid", SqlDbType.Char, 10).Value = BE.tablaid;
cmd.Parameters.Add("@codigoid", SqlDbType.Char, 4).Value = BE.codigoid;
cmd.Parameters.Add("@nomlike1", SqlDbType.VarChar, 500).Value = BE.nomlike1;
cmd.Parameters.Add("@nomlike2", SqlDbType.VarChar, 500).Value = BE.nomlike2;
cmd.Parameters.Add("@nomlike3", SqlDbType.VarChar, 500).Value = BE.nomlike3;
cmd.Parameters.Add("@norden", SqlDbType.Int).Value = BE.norden;
cmd.Parameters.Add("@incluir_blanco", SqlDbType.Int).Value = BE.incluir_blanco;
}
cnx.Open();
try
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
return ds;
}
catch (Exception ex)
{
Sql_Error = ex.Message;
throw new Exception(ex.Message);
}
}
}
}
public static DataSet ExecuteDataSet(CommandType cmdType, string strProcedureName, SqlParameter[] objParameters)
{
DataSet dset = new DataSet();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db1ConnectionString"].ToString());
con.Open();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = cmdType;
cmd.CommandText = strProcedureName;
AttachParameters(cmd, objParameters);
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dset);
return dset;
}
catch (Exception ex)
{
return dset;
}
finally
{
con.Close();
}
}
public DataSet GetAll(string empresaid, tb_co_tabla01_mediopago BE)
{
using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
{
using (SqlCommand cmd = new SqlCommand("gspTbCoTabla01Mediopago_SEARCH", cnx))
{
DataSet ds = new DataSet();
{
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@codigoid", SqlDbType.Char, 3).Value = BE.codigoid;
cmd.Parameters.Add("@descripcion", SqlDbType.VarChar, 100).Value = BE.descripcion;
cmd.Parameters.Add("@sigla", SqlDbType.Char, 3).Value = BE.sigla;
cmd.Parameters.Add("@usuar", SqlDbType.Char, 15).Value = BE.usuar;
}
try
{
cnx.Open();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
public IList<Models.Auto> GetAllAutos()
{
DataSet ds = new DataSet();
myCommand = new SqlDataAdapter("Select * from Auto", myConnection);
ds = new DataSet();
myCommand.Fill(ds);
IList<Models.Auto> AutoList = new List<Models.Auto>();
foreach (DataRow item in ds.Tables[0].Rows)
{
AutoList.Add(new Auto
{
AutoNumber = item["AutoNumber"].ToString(),
AutoName = item["AutoName"].ToString()
});
}
ds.Dispose();
ds = null;
return AutoList;
}
public string InsertKhachHang(string TenKH, string Gioitinh, string Diachi, string SDT, string LoaiKH, string Ghichu)
{
string str = "";
string sql = "InsertKH";
// string sql = "insert NhomSP values (@Manhom, @Tennhom, @NgayCN)";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TenKH", TenKH);
cmd.Parameters.AddWithValue("@gt", Gioitinh);
cmd.Parameters.AddWithValue("@DC", Diachi);
cmd.Parameters.AddWithValue("@SDT", SDT);
cmd.Parameters.AddWithValue("@LoaiKH", LoaiKH);
cmd.Parameters.AddWithValue("@ghichu", Ghichu);
//cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
str = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
return str;
}
public static DataTable GetAllCidades(int estado_id)
{
DataTable retorno = new DataTable();
StringBuilder SQL = new StringBuilder();
SQL.Append(@"SELECT CidadeId, Nome FROM Cidade WHERE EstadoId = @ESTADO_ID");
try
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mendes_varejo"].ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL.ToString(), connection);
command.Parameters.AddWithValue("@ESTADO_ID", estado_id);
command.ExecuteNonQuery();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(retorno);
}
}
catch (Exception erro)
{
throw erro;
}
return retorno;
}
public DataTable ObtenerDatos(String ComandoSql)
{
SqlDataAdapter adapter;
DataTable dt = new DataTable();
try
{
con.Open();
Cmd.Connection = con;
Cmd.CommandText = ComandoSql;
adapter = new SqlDataAdapter(Cmd);
adapter.Fill(dt);
}
catch (Exception ex)
{
throw ex;
//MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
return dt;
}
private void button1_Click_1(object sender, EventArgs e)
{
SqlConnection con1 = new SqlConnection(constring);
con1.Open();
SqlDataAdapter adp = new SqlDataAdapter("Select whoislogin from Registration", con1);
DataSet ds = new DataSet();
adp.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
SqlCommand cmd = new SqlCommand("Update Registration set whoislogin='******'", con1);
cmd.ExecuteNonQuery();
}
SqlDataAdapter adp2 = new SqlDataAdapter("Select * from ResManagerToAdmin", con1);
DataSet ds2 = new DataSet();
adp2.Fill(ds2);
for (int j = 0; j < ds2.Tables[0].Rows.Count; j++)
{
//adminview = ds.Tables[0].Rows[j]["RView"].ToString();
SqlCommand cmd = new SqlCommand("Update ResManagerToAdmin set RView='" + adminviewempty + "'", con1);
cmd.ExecuteNonQuery();
}
con1.Close();
this.Close();
}
protected int getTotalCount()
{
SqlConnection connection = new SqlConnection(GetConnectionString());
DataTable dt = new DataTable();
try
{
connection.Open();
string sqlStatement = "SELECT * FROM tblContact";
SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
return dt.Rows.Count;
}
// This function will be used to execute R(CRUD) operation of parameterized commands
internal static DataTable ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param)
{
DataTable table = new DataTable();
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(param);
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
}
catch
{
throw;
}
}
}
return table;
}
protected void load_price()
{
string price_name = null;
string article = null;
price_name = Request.QueryString["price_name"];
article = Request.QueryString["article"];
if (article != null)
{
if (price_name != null)
{
SqlConnection con = new SqlConnection(str);
string sql = "SELECT id, article, ROUND([price]*(SELECT course FROM Course_USD WHERE id = 1),2) AS [price] FROM " + price_name + " WHERE article=" + article + ";";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds, price_name);
DataRow dr;
dr = ds.Tables[price_name].Rows[0];
//Заполняем стоимость товара:
lblprice.Text = dr["price"].ToString();
}
else {Response.Redirect("Error.aspx");}
}
else {Response.Redirect("Error.aspx"); }
}
public void devuelveCodigo()
{
SqlConnection con = conex.Conecta();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da = new SqlDataAdapter("SELECT * FROM Vista_PagoH WHERE CLT_RUT='" + txt_rut.Text + "'", conex.Conecta());
da.Fill(ds, "0");
string digito = ds.Tables[0].Rows[0]["CLT_DV"].ToString();
string razon = ds.Tables[0].Rows[0]["CLT_RAZON"].ToString();
string direccion = ds.Tables[0].Rows[0]["CLT_DIREC"].ToString();
string ciudad = ds.Tables[0].Rows[0]["CLT_CIUDAD"].ToString();
string cupo = ds.Tables[0].Rows[0]["CLT_CUPO"].ToString();
string fpago = ds.Tables[0].Rows[0]["CLT_PAGO"].ToString();
string convenio = ds.Tables[0].Rows[0]["DES_FORPAG"].ToString();
int cupo2 = Convert.ToInt32(cupo);
txt_digito.Text = digito;
txt_nombre.Text = razon;
txt_direccion.Text = direccion;
txt_ciudad.Text = ciudad;
txt_convenio.Text = convenio;
txt_fpago.Text = "Fecha de Pago : " + fpago + " de C/Mes.";
txt_cupo.Text = "Cupo Disponible $ " + Convert.ToString(cupo2.ToString("N0")) + "";
inferior(txt_rut.Text);
}
/// <summary>
/// Get InvoiceNo Corresponding to Ledger For PurchaseReturn Register
/// </summary>
/// <param name="decLedgerId"></param>
/// <returns></returns>
public List<DataTable> GetInvoiceNoCorrespondingtoLedgerForPurchaseReturnReport(decimal decLedgerId, decimal decVoucherId)
{
List<DataTable> ListObj = new List<DataTable>();
DataTable dtbl = new DataTable();
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlDataAdapter sqldataadapter = new SqlDataAdapter("GetInvoiceNoCorrespondingtoLedgerForPurchaseReturnReport", sqlcon);
sqldataadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter sqlparameter = new SqlParameter();
sqlparameter = sqldataadapter.SelectCommand.Parameters.Add("@ledgerId", SqlDbType.Decimal);
sqlparameter.Value = decLedgerId;
sqlparameter = sqldataadapter.SelectCommand.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sqlparameter.Value = decVoucherId;
sqldataadapter.Fill(dtbl);
ListObj.Add(dtbl);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
sqlcon.Close();
}
return ListObj;
}
protected void LoadData()
{
db dbc = new db();
string query = "SELECT * FROM pages ORDER BY id DESC";
dbc.cmd.CommandText = query;
SqlDataAdapter da = new SqlDataAdapter(dbc.cmd);
DataTable dt = new DataTable();
da.Fill(dt);
PagedDataSource pgitems = new PagedDataSource();
DataView dv = new DataView(dt);
pgitems.DataSource = dv;
pgitems.AllowPaging = true;
pgitems.PageSize = 9;
pgitems.CurrentPageIndex = PageNumber;
if (pgitems.PageCount > 1)
{
rptPages.Visible = true;
ArrayList pages = new ArrayList();
for (int i = 0; i < pgitems.PageCount; i++)
pages.Add((i + 1).ToString());
rptPages.DataSource = pages;
rptPages.DataBind();
}
else
rptPages.Visible = false;
rptContent.DataSource = pgitems;
rptContent.DataBind();
}
public List<getPlace> getallplace(string cityName)
{
try
{
List<getPlace> list = new List<getPlace>();
SqlConnection con = new SqlConnection();
con.ConnectionString = @"Data Source=MOHIT-PC\SQLEXPRESS;Initial Catalog=travelogue;User ID=sa;Password=patel";
string query = "select p.place_name from Place_Detail as p where p.CT_id = (select c.CT_id From City_Detail as c where c.CT_Name = @CityName) ";
SqlCommand cmd = new SqlCommand(query, con);
SqlParameter p = new SqlParameter("@CityName", cityName);
cmd.Parameters.Add(p);
con.Open();
SqlDataAdapter adp = new SqlDataAdapter();
DataTable dt = new DataTable();
adp.SelectCommand = cmd;
adp.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
getPlace obj = new getPlace();
obj.CPlace = dr["Place_Name"].ToString();
list.Add(obj);
}
return list;
}
catch (Exception e)
{
return new List<getPlace>();
}
}
private void ejecutarQuery(string query)
{
Conexion con = new Conexion();
con.cnn.Open();
DataTable dtDatos = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(query, con.cnn);
da.Fill(dtDatos);
dt = dtDatos;
dgvResults.DataSource = dtDatos;
if(consulta == "D")
{
//CAMBIO COLUMNA DE NUM_TARJETA
SqlCommand command = new SqlCommand(query, con.cnn);
SqlDataReader lector = command.ExecuteReader();
lector.Read();
foreach (DataGridViewRow row in dgvResults.Rows)
{
string ultimosCuatro = lector.GetString(4);
row.Cells["num_tarjeta"].Value = "XXXX-XXXX-XXXX-" + ultimosCuatro.Remove(0, 12);
}
con.cnn.Close();
}
}
public DataSet GetAll(string empresaid, tb_tipimpto BE)
{
using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
{
using (SqlCommand cmd = new SqlCommand("gspTbTipimpto_SEARCH", cnx))
{
DataSet ds = new DataSet();
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tipimptoid", SqlDbType.Char, 1).Value = BE.tipimptoid;
cmd.Parameters.Add("@status", SqlDbType.Bit).Value = BE.status;
}
try
{
cnx.Open();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
private void HeadData()
{
sb = new StringBuilder();
String sql;
sb.Remove(0, sb.Length);
sb.Append("SELECT CUSTOMER.CustomerName, CUSTOMER.CustomerAddress,");
sb.Append("RECEIVABLE.ReceivableDate,PRODUCT.ProductName, SALE_HEAD.SaleHeadId, ");
sb.Append("SALE_DETAIL.SaleDetailQuantity, UNITSALE.UnitSaleName, SALE_DETAIL.SaleDetailPriceUnit ,RECEIVABLE.PayCash, ");
sb.Append("RECEIVABLE.PayBank, ");
sb.Append("(SALE_DETAIL.SaleDetailQuantity) * (SALE_DETAIL.SaleDetailPriceUnit) AS Total ,");
sb.Append("RECEIVABLE.ReceivebleTopic ");
sb.Append("FROM RECEIVABLE LEFT JOIN ");
sb.Append("PRODUCT ON RECEIVABLE.ProductId = PRODUCT.ProductId LEFT JOIN ");
sb.Append("CUSTOMER ON RECEIVABLE.CustomerId = CUSTOMER.CustomerId LEFT JOIN ");
sb.Append("SALE_HEAD ON RECEIVABLE.SaleHeadId = SALE_HEAD.SaleHeadId AND CUSTOMER.CustomerId = SALE_HEAD.CustomerId LEFT JOIN ");
sb.Append(" SALE_DETAIL ON SALE_HEAD.SaleHeadId = SALE_DETAIL.SaleHeadId LEFT JOIN ");
sb.Append(" UNITSALE ON PRODUCT.UnitSaleId = UNITSALE.UnitSaleId ");
sb.Append("WHERE (CUSTOMER.CustomerId = @SaleId)");
sql = sb.ToString();
com = new SqlCommand(sql, Conn);
com.Parameters.Add("@SaleId", SqlDbType.Int).Value = SaleId;
//
SaleDa = new SqlDataAdapter(com);
SaleDa.Fill(SaleDataset, "Receivable");
Conn.Close();
Report.CtrReceivable rp = new Report.CtrReceivable();
rp.SetDataSource(SaleDataset.Tables["Receivable"]);
CtrReveivable.ReportSource = rp;
CtrReveivable.Refresh();
CtrReveivable.Show();
}
public DataSet _dataSet_NoParameter(string queryString, string connString)
{
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;
if (conn.State == ConnectionState.Closed) conn.Open();
SqlCommand comm = new SqlCommand(queryString, conn);
daRecords = new SqlDataAdapter(comm);
daRecords.Fill(dsRecords);
return dsRecords;
}
catch (SqlException er)
{
sqlUserError = er.Message;
return dsRecords;
}
finally
{
dtRecords.Dispose();
daRecords.Dispose();
dsRecords.Dispose();
}
}
public DataTable listarSucursales()
{
SqlDataAdapter da = new SqlDataAdapter("select * from tb_sucursal", cn.getCn);
DataTable tb = new DataTable();
da.Fill(tb);
return tb;
}
public static string GetCompanyData()
{
string connString = ConnectionStringFactory.NXJCConnectionString; //填写链接字符串
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connString))
{
//conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT KeyID,CementTypes,vDate,OutputFirstTeam,OutputSecondTeam,OutputThirdTeam,OutputAmountto FROM table_CementMillProductionLineMonthlyOutput"; //填写查询语句
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
DataGridColumnType columnType = new DataGridColumnType
{
ColumnText = new string[] { "ID", "日期", "水泥品种", "甲班产量", "乙班产量", "丙班产量", "合计产量" }, //填写表头及宽度
ColumnWidth = new int[] { 10, 100, 100, 100,100, 100, 100 },
ColumnType = new string[] { "", "", "", "", "", "","" }
};
string result = ReportTemplateHelper.GetDataGridTemplate(dt, columnType);
return result;
}
public List<model1> getinfo(string placeName)
{
SqlConnection con = new SqlConnection();
List<model1> list = new List<model1>();
con.ConnectionString = @"Data Source=MOHIT-PC\SQLEXPRESS;Initial Catalog=travelogue;User ID=sa;Password=patel";
string query = "select intro,lat,long from Place_Detail where Place_Name = @PlaceName";
SqlCommand cmd = new SqlCommand(query, con);
SqlParameter p = new SqlParameter("@PlaceName", placeName);
cmd.Parameters.Add(p);
con.Open();
SqlDataAdapter adp = new SqlDataAdapter();
DataTable dt = new DataTable();
adp.SelectCommand = cmd;
adp.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
model1 m = new model1();
m.intro = dt.Rows[0]["intro"].ToString();
m.lat = dt.Rows[0]["lat"].ToString();
m.longi = dt.Rows[0]["long"].ToString();
list.Add(m);
}
con.Close();
return list;
}
public CentroMedicoDataSet.UsuariosDataTable getUsersByCrit(String criterio)
{
criterio.Trim();
SqlConnection conexion = new SqlConnection(strConexion);
conexion.Open();
StringBuilder query = new StringBuilder();
if (criterio.Equals(""))
{
query.AppendFormat("select * from usuarios");
}
else
{
query.AppendFormat("select * from usuarios where NssUsuario like '%{0}%' or nombre like '%{0}%' or apellidos like '%{0}%' or direccion like '%{0}%' or localidad like '%{0}%' or telefono like '%{0}%' or dni like '%{0}%' or email like '%{0}%'", criterio);
}
SqlDataAdapter adapter = new SqlDataAdapter(query.ToString(), conexion);
CentroMedicoDataSet.UsuariosDataTable tabla = new CentroMedicoDataSet.UsuariosDataTable();
adapter.Fill(tabla);
conexion.Close();
return tabla;
}
private void ShowSolUser_Load(object sender, EventArgs e)
{
// TODO: esta línea de código carga datos en la tabla 'rEV_DataBaseDataSet.SolUser' Puede moverla o quitarla según sea necesario.
this.solUserTableAdapter.Fill(this.rEV_DataBaseDataSet.SolUser);
System.Data.SqlClient.SqlDataAdapter asd = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM SolUser WHERE codUsr = '******' ;", cs.GetCONN());
DataTable dt = new DataTable();
asd.Fill(dt);
dataGridUsr.DataSource = dt;
}
public DataTable QuerySqlDataTable(string sql)
{
using (var con = CreateCon())
{
DataTable table = new DataTable();
System.Data.SqlClient.SqlDataAdapter dapt = new System.Data.SqlClient.SqlDataAdapter(sql, con);
dapt.Fill(table);
return(table);
}
}
private void frmEditData_Load(object sender, System.EventArgs e)
{
//cnStudent = new SqlConnection("");
string strSql = "select stu_id,name,sex,addr from student";
daStudent = new SqlDataAdapter(strSql, cnStudent);
//DataSet dsStudent = new DataSet();
this.daStudent.Fill(this.dsStudent1, "student");
UpdateDisplay();
}
public DataSet getDataSet()
{
DataSet ds = new DataSet();
System.Data.IDbDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter();
dataAdapter.SelectCommand = _cmd;
dataAdapter.Fill(ds);
return(ds);
}
//---------------------------------------------------------------------
private void TestUnTyped()
{
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
System.Data.DataSet dataset1 = new System.Data.DataSet();
dataset1.Tables.Add(new System.Data.DataTable("Customers"));
//<Snippet6>
sqlDataAdapter1.Fill(dataset1.Tables["Customers"]);
//</Snippet6>
}
internal DataSet GetData(string sqlStatement, string tableName = "table")
{
sql.SqlConnection cnn = new sql.SqlConnection(Properties.Settings.Default.cnnString);
sql.SqlCommand stmnt = new sql.SqlCommand(sqlStatement, cnn);
sql.SqlDataAdapter da = new sql.SqlDataAdapter(stmnt);
DataSet ds = new DataSet();
da.Fill(ds, tableName);
return(ds);
}
private void bReset_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection SQLCon = new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=SWENDatabase;Integrated Security=True ");
System.Data.DataTable Guestdata = new System.Data.DataTable();
string query = "SELECT * from Guest";
System.Data.SqlClient.SqlDataAdapter SQLAdapter = new System.Data.SqlClient.SqlDataAdapter(query, SQLCon);
SQLAdapter.Fill(Guestdata);
GridGuestInfo.DataSource = Guestdata;
}
public DataSet QuerySqlDataSet(string sql)
{
using (var con = CreateCon())
{
DataSet ds = new DataSet();
System.Data.SqlClient.SqlDataAdapter dapt = new System.Data.SqlClient.SqlDataAdapter(sql, con);
dapt.Fill(ds);
return(ds);
}
}
public static DataSet GetData(string sql)
{
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter
(sql, ConnectionString);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
public ConnectionManagement()
{
stringConnection = $"Data Source={ServerName};Initial Catalog={DatabaseName};Integrated Security=True";
connection = new SqlConnection();
adapter = new SqlDataAdapter();
command = new SqlCommand();
commandBuilder = new SqlCommandBuilder();
dataSet = new DataSet();
dataTable = new DataTable();
}
public System.Data.DataTable getoperarios()
{
string conect = GetOrigenope(intSUCURSALID);
System.Data.DataTable Tabla = new System.Data.DataTable();
string strFuncion = Funcion(intSUCURSALID);
try
{
using (System.Data.SqlClient.SqlConnection Cnn = new System.Data.SqlClient.SqlConnection(conect))
{
//Cnn.ConnectionTimeout = 500;
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(strFuncion, Cnn))
{
try
{
//
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = strFuncion;
Cmd.CommandTimeout = 0;
System.Data.SqlClient.SqlDataAdapter Datos = new System.Data.SqlClient.SqlDataAdapter(Cmd);
Cnn.Open();
Datos.Fill(Tabla);
return(Tabla);
}
catch (Exception ex)
{
throw new Exception("Error de acceso a datos, Error: " + ex.ToString());
}
finally
{
if (Cnn.State == ConnectionState.Open)
{
Cnn.Close();
}
}
}
}
}
catch (Exception z)
{
RadWindow radWindow = new RadWindow();
RadWindow.Alert(new DialogParameters()
{
Content = "Error al acceso de datos .",
Header = "BIG",
DialogStartupLocation = WindowStartupLocation.CenterOwner
// IconContent = "";
});
return(Tabla);
}
}
public void SaveComment_clarification(string _Type, string _iDocsendDetailsID, string _sDocumentName, string refno)
{
SendRecDoc objDoc = new SendRecDoc();
string PuserID = ((UserDetails)Session[clsConstant.TOKEN]).UserID.ToString();
string sqlto = "select tblUserMaster.iUserID from tblUserMaster ";
sqlto = sqlto + " join tblDocSendDetail on tblDocSendDetail.iUserID=tblUserMaster.iUserID ";
sqlto = sqlto + " where iDocSendID =" + _iDocsendDetailsID + " and bTOCC=1 ";
SqlConnection cntouser = new SqlConnection(ConfigurationManager.ConnectionStrings["TA7511DBConnString"].ConnectionString);
SqlDataAdapter adtouser = new System.Data.SqlClient.SqlDataAdapter(sqlto, cntouser);
DataSet dstouser = new DataSet();
adtouser.Fill(dstouser);
for (int i = 0; i <= dstouser.Tables[0].Rows.Count - 1; i++)
{
string userto = dstouser.Tables[0].Rows[i]["iUserID"].ToString();
string sqlmcid = " select imcid from tblDocSend where iDocSendID=" + _iDocsendDetailsID;
SqlConnection mcidcon = new SqlConnection(ConfigurationManager.ConnectionStrings["TA7511DBConnString"].ConnectionString);
SqlDataAdapter mciad = new System.Data.SqlClient.SqlDataAdapter(sqlmcid, mcidcon);
DataSet dsmcid = new DataSet();
mciad.Fill(dsmcid);
string emailAlert = string.Empty;
DataSet dsuserEmail = new DataSet();
dsuserEmail = objDoc.getmailforrejectApprove(int.Parse(dsmcid.Tables[0].Rows[0]["imcid"].ToString()));
emailAlert += GetFromID(int.Parse(dsmcid.Tables[0].Rows[0]["imcid"].ToString())) + ';';
emailAlert += dsuserEmail.Tables[0].Rows[0]["selectusr"].ToString() + ';';
emailAlert = FindEmailID(emailAlert, ((UserDetails)(Session[clsConstant.TOKEN])).EmailID) + ';';
if (emailAlert.Contains("*****@*****.**"))
{
emailAlert += "*****@*****.**" + ';';
}
Session["userEmail"] = emailAlert;
if (objDoc.saveALLComment(dsmcid.Tables[0].Rows[0]["imcid"].ToString(), _iDocsendDetailsID, "R", _sDocumentName, refno, DateTime.Now, int.Parse(PuserID), userto, "") > 0)
{
Utility objUtl = new Utility();
try
{
string nameOfSender = ((UserDetails)Session[clsConstant.TOKEN]).FirstName.ToString() + ' ' + ((UserDetails)Session[clsConstant.TOKEN]).LastName.ToString();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
//CommonFunctions.Connection = new System.Data.SqlClient.SqlConnection();
this.UserSet = new Vacations.UserDataset();
this.UserAdapter = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
((System.ComponentModel.ISupportInitialize)(this.UserSet)).BeginInit();
//
// CommonFunctions.Connection
//
//CommonFunctions.Connection.ConnectionString = "workstation id=MAIN;packet size=4096;integrated security=SSPI;data source=MAIN;pe" +
//"rsist security info=False;initial catalog=Vacations";
//
// UserSet
//
this.UserSet.DataSetName = "UserDataset";
this.UserSet.Locale = new System.Globalization.CultureInfo("en-US");
//
// UserAdapter
//
this.UserAdapter.SelectCommand = this.sqlSelectCommand1;
this.UserAdapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Users", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ID", "ID"),
new System.Data.Common.DataColumnMapping("Username", "Username"),
new System.Data.Common.DataColumnMapping("PasswordSalt", "PasswordSalt"),
new System.Data.Common.DataColumnMapping("Repeats", "Repeats"),
new System.Data.Common.DataColumnMapping("PasswordHash", "PasswordHash"),
new System.Data.Common.DataColumnMapping("Email", "Email"),
new System.Data.Common.DataColumnMapping("FirstName", "FirstName"),
new System.Data.Common.DataColumnMapping("LastName", "LastName"),
new System.Data.Common.DataColumnMapping("Address", "Address"),
new System.Data.Common.DataColumnMapping("City", "City"),
new System.Data.Common.DataColumnMapping("State", "State"),
new System.Data.Common.DataColumnMapping("Zip", "Zip"),
new System.Data.Common.DataColumnMapping("Country", "Country"),
new System.Data.Common.DataColumnMapping("PrimaryTelephone", "PrimaryTelephone"),
new System.Data.Common.DataColumnMapping("EveningTelephone", "EveningTelephone"),
new System.Data.Common.DataColumnMapping("DaytimeTelephone", "DaytimeTelephone"),
new System.Data.Common.DataColumnMapping("MobileTelephone", "MobileTelephone"),
new System.Data.Common.DataColumnMapping("Website", "Website"),
new System.Data.Common.DataColumnMapping("IfAdmin", "IfAdmin"),
new System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
new System.Data.Common.DataColumnMapping("Registered", "Registered"),
new System.Data.Common.DataColumnMapping("IfPayTravelAgents", "IfPayTravelAgents")
})
});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = @"SELECT ID, Username, PasswordSalt, Repeats, PasswordHash, Email, FirstName, LastName, Address, City, State, Zip, Country, PrimaryTelephone, EveningTelephone, DaytimeTelephone, MobileTelephone, Website, IfAdmin, CompanyName, Registered, IfPayTravelAgents FROM Users WHERE (ID <> @UserID)";
this.sqlSelectCommand1.Connection = CommonFunctions.GetConnection();
this.sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", System.Data.SqlDbType.Int, 4, "ID"));
((System.ComponentModel.ISupportInitialize)(this.UserSet)).EndInit();
}
private System.Data.DataSet MyDataSet()
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
con.Open();
System.Data.SqlClient.SqlDataAdapter da_1;
da_1 = new System.Data.SqlClient.SqlDataAdapter("AllItemsPlanner", con);
System.Data.DataSet dat_set = new System.Data.DataSet();
da_1.Fill(dat_set, "Planner");
con.Close();
return(dat_set);
}
public DataTable CreateDataTable(SqlCommand command)
{
DataTable dataTable = new DataTable();
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command);
da.Fill(dataTable);
da.Dispose();
da = null;
return(dataTable);
}
public static void setupProdAdapterCart()
{
con.ConnectionString = ConStr;
prodCmd.Connection = con;
prodCmd.CommandType = System.Data.CommandType.Text;
prodCmd.CommandText = "Select * from Product order by CartProdID";
ProdAdapter = new System.Data.SqlClient.SqlDataAdapter(prodCmd);
ProdAdapter.FillSchema(tblCart, System.Data.SchemaType.Source);
}
public DataSet return_dataset(string sql)
{
open_db();
DataTable worktable = new DataTable();
SqlDataAdapter dap = new System.Data.SqlClient.SqlDataAdapter(new System.Data.SqlClient.SqlCommand(sql, cn));
DataSet ds = new DataSet();
dap.Fill(ds);
close_conn();
return(ds);
}
public Esito AggiornaDatiAgendaTender(DatiTender datiAgendaTender)
{
Esito esito = new Esito();
Anag_Utenti utente = ((Anag_Utenti)HttpContext.Current.Session[SessionManager.UTENTE]);
try
{
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(sqlConstr))
{
using (System.Data.SqlClient.SqlCommand StoreProc = new System.Data.SqlClient.SqlCommand("UpdateDatiTender"))
{
using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter())
{
StoreProc.Connection = con;
sda.SelectCommand = StoreProc;
StoreProc.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter id = new System.Data.SqlClient.SqlParameter("@id", datiAgendaTender.Id);
id.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(id);
// PARAMETRI PER LOG UTENTE
SqlParameter idUtente = new SqlParameter("@idUtente", utente.id);
idUtente.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(idUtente);
SqlParameter nomeUtente = new SqlParameter("@nomeUtente", utente.username);
nomeUtente.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(nomeUtente);
// FINE PARAMETRI PER LOG UTENTE
SqlParameter idDatiAgenda = new SqlParameter("@idDatiAgenda", datiAgendaTender.IdDatiAgenda);
idDatiAgenda.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(idDatiAgenda);
SqlParameter idTender = new SqlParameter("@idTender", datiAgendaTender.IdTender);
idTender.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(idTender);
StoreProc.Connection.Open();
int iReturn = StoreProc.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
esito.Codice = Esito.ESITO_KO_ERRORE_SCRITTURA_TABELLA;
esito.Descrizione = "Dati_Tender_DAL.cs - AggiornaDatiAgendaTender " + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace;
}
return(esito);
}
/// <summary>
/// 执行查询，返回一个 DataTable对象
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable GetDataTable(string sql)
{
SqlCommand _command = new SqlCommand(sql, _conn);
_command.CommandTimeout = 90;
DataTable dt = new DataTable();
System.Data.Common.DbDataAdapter Adapter = new System.Data.SqlClient.SqlDataAdapter(_command);
Adapter.Fill(dt);
return(dt);
}
public void List()
{
string StartDate = "StartDate".RequestStr();
string EndDate = "EndDate".RequestStr().ToDateTime().ToString("yyyy-MM-dd");
#region SQL
string Sql = @" --sass
SELECT
d.*,convert(varchar,d.ReportDate,23) as ReportDate1,
d.F_InvalidItem as F_InvalidItem1,d.F_InvalidItem as F_InvalidItem2,
case
when AdditionalQualified =0 then '未合格'
when AdditionalQualified =1 then '已合格'
end as st
FROM v_invalid_document d
left outer join sys_module on d.ModelIndex = sys_module.id
where F_InvalidItem NOT LIKE '%#%'
and ReportDate between '{0}' AND '{1}'
";
#endregion
Sql = string.Format(Sql, StartDate, EndDate);
BLL_Document BLL = new BLL_Document();
DataSet Ds = new DataSet();
using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
{
Conn.Open();
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
{
using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
{
Adp.Fill(Ds);
}
}
Conn.Close();
}
string Json = JsonConvert.SerializeObject(Ds.Tables[0]);
Json = "{\"rows\":" + Json + ",\"total\":0}";
Response.Write(Json);
Response.End();
}
public System.Data.DataTable getventas(int selescted)
{
System.Data.DataTable Tabla = new System.Data.DataTable();
string conect = GetOrigen(selescted);
string strFuncion = Funcion(selescted);
try
{
using (System.Data.SqlClient.SqlConnection Cnn = new System.Data.SqlClient.SqlConnection(conect))
{
//Cnn.ConnectionTimeout = 500;
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(strFuncion, Cnn))
{
try
{
//
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = strFuncion;
Cmd.CommandTimeout = 0;
System.Data.SqlClient.SqlDataAdapter Datos = new System.Data.SqlClient.SqlDataAdapter(Cmd);
Cnn.Open();
Datos.Fill(Tabla);
return(Tabla);
}
catch (Exception ex)
{
return(Tabla);
}
finally
{
if (Cnn.State == ConnectionState.Open)
{
Cnn.Close();
}
}
}
}
}
catch (Exception ex)
{
return(Tabla);
RadWindow radWindow = new RadWindow();
RadWindow.Alert(new DialogParameters()
{
Content = "Los parámetros son inválidos.",
Header = "BIG",
DialogStartupLocation = WindowStartupLocation.CenterOwner
// IconContent = "";
});
}
}
private System.Data.DataSet MyDataSet()
{
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon))
{
con.Open();
da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);
System.Data.DataSet dat_set = new System.Data.DataSet();
da_1.Fill(dat_set, "Table_Data_1");
return(dat_set);
}
}
protected void BtnCargaInicial_Click(object sender, EventArgs e)
{
System.Data.DataSet _ds = new System.Data.DataSet();
System.Data.SqlClient.SqlDataAdapter _da = new System.Data.SqlClient.SqlDataAdapter("TodasLasMascotas", "Data Source=.; Initial Catalog = Veterinaria; Integrated Security = true");
_da.Fill(_ds, "Mascotas");
ViewState["Mascotas"] = _ds.Tables["Mascotas"];
GVMostrarDesdeBD.DataSource = (System.Data.DataTable)ViewState["Mascotas"];
GVMostrarDesdeBD.DataBind();
}
public static DataSet ExecuteQuery(string server, string database, string query)
{
DataSet resultsDataSet = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection($"Server = {server}; Database = {database}; Integrated Security = True"))
{
SqlDataAdapter sqlAdapter = new System.Data.SqlClient.SqlDataAdapter(query, sqlConnection);
int rowsAffected = sqlAdapter.Fill(resultsDataSet);
}
return(resultsDataSet);
}
public DataSet GetDataSet(string sql)
{
System.Data.SqlClient.SqlConnection conn3 = new System.Data.SqlClient.SqlConnection(cnStr);
System.Data.SqlClient.SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(sql, conn3);
DataSet ds = new DataSet();
adapt.Fill(ds);
conn3.Close();
conn3.Dispose();
conn3 = null;
return(ds);
}
public SqlDbOperHandler()
{
string dbConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["DataBase"].ConnectionString.ToString();
System.Data.SqlClient.SqlConnection _conn = new System.Data.SqlClient.SqlConnection(dbConnStr);
conn = _conn;
dbType = DatabaseType.SqlServer;
conn.Open();
cmd = conn.CreateCommand();
da = new System.Data.SqlClient.SqlDataAdapter();
}
/// <summary>
/// Executes a Transact-SQL statement against the connection and returns represents one table of in-memory data
/// </summary>
/// <param name="ExecuteString">Executes the Transact-SQL statement or stored procedure against the connection.</param>
/// <returns>An DataTable object</returns>
public System.Data.DataTable ExecuteDataTable(string ExecuteString)
{
using (PrepareExecution(ExecuteString))
{
using (System.Data.SqlClient.SqlDataAdapter SqlAdp = new System.Data.SqlClient.SqlDataAdapter(SqlCmd))
{
System.Data.DataSet dataSet = new System.Data.DataSet();
SqlAdp.Fill(dataSet);
return(dataSet.Tables[0]);
}
}
}
public System.Data.DataSet GetDataSet(string ExecuteString, ref DataSet dataSet, string tblname)
{
using (PrepareExecution(ExecuteString))
{
using (System.Data.SqlClient.SqlDataAdapter SqlAdp = new System.Data.SqlClient.SqlDataAdapter(SqlCmd))
{
//System.Data.DataSet dataSet = new System.Data.DataSet();
SqlAdp.Fill(dataSet, tblname);
return(dataSet);
}
}
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
this.dataAdapterEmp = new System.Data.SqlClient.SqlDataAdapter();
this.connEmp = new System.Data.SqlClient.SqlConnection();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.dataSetEmp = new ControlsBookWeb.Ch07.dataSetEmp();
((System.ComponentModel.ISupportInitialize)(this.dataSetEmp)).BeginInit();
//
// dataAdapterEmp
//
this.dataAdapterEmp.SelectCommand = this.sqlSelectCommand1;
this.dataAdapterEmp.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Employees", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("EmployeeID", "EmployeeID"),
new System.Data.Common.DataColumnMapping("LastName", "LastName"),
new System.Data.Common.DataColumnMapping("FirstName", "FirstName"),
new System.Data.Common.DataColumnMapping("Title", "Title"),
new System.Data.Common.DataColumnMapping("TitleOfCourtesy", "TitleOfCourtesy"),
new System.Data.Common.DataColumnMapping("BirthDate", "BirthDate"),
new System.Data.Common.DataColumnMapping("HireDate", "HireDate"),
new System.Data.Common.DataColumnMapping("Address", "Address"),
new System.Data.Common.DataColumnMapping("City", "City"),
new System.Data.Common.DataColumnMapping("Region", "Region"),
new System.Data.Common.DataColumnMapping("PostalCode", "PostalCode"),
new System.Data.Common.DataColumnMapping("Country", "Country"),
new System.Data.Common.DataColumnMapping("HomePhone", "HomePhone"),
new System.Data.Common.DataColumnMapping("Extension", "Extension"),
new System.Data.Common.DataColumnMapping("Photo", "Photo"),
new System.Data.Common.DataColumnMapping("Notes", "Notes"),
new System.Data.Common.DataColumnMapping("ReportsTo", "ReportsTo"),
new System.Data.Common.DataColumnMapping("PhotoPath", "PhotoPath")
})
});
//
// connEmp
//
this.connEmp.ConnectionString = ((string)(configurationAppSettings.GetValue("connEmp.ConnectionString", typeof(string))));
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDa" +
"te, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Not" +
"es, ReportsTo, PhotoPath FROM Employees";
this.sqlSelectCommand1.Connection = this.connEmp;
//
// dataSetEmp
//
this.dataSetEmp.DataSetName = "dataSetEmp";
this.dataSetEmp.Locale = new System.Globalization.CultureInfo("en-US");
this.Load += new System.EventHandler(this.Page_Load);
((System.ComponentModel.ISupportInitialize)(this.dataSetEmp)).EndInit();
}
static void Main(string[] args)
{
String connString = @"Server = INSTRUCTOR-403\SQLEXPRESS; Database = Libros; Trusted_Connection = True;";
SqlConnection conn = new SqlConnection(connString);
String SQlCmd = "select * " +
"from editorial inner join libros " +
"on editorial.editorial_id = libros.Editorial_ID " +
" " +
"select * from libros " +
"select * from editorial" +
" ";
conn.Open();
var comando = conn.CreateCommand();
comando.CommandText = SQlCmd;
SqlDataReader lector = comando.ExecuteReader();
lector.Read();
lector.Close();
System.Data.IDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(SQlCmd, conn);
DataSet ds = new DataSet("Libros");
da.Fill(ds);
ds.Tables[0].TableName = "eljoin";
ds.Tables[1].TableName = "libros";
ds.Tables[2].TableName = "editorial";
foreach (DataTable dt in ds.Tables)
{
Console.WriteLine(dt.TableName);
foreach (DataColumn col in dt.Columns)
{
Console.Write(" ");
Console.WriteLine(col.ColumnName);
Console.Write(" ");
Console.WriteLine(col.GetType());
}
}
ds.WriteXml(@"C:\clase10\dumpLibros.xml");
ds.WriteXmlSchema(@"C:\clase10\dumpSchema.xml");
conn.Close();
Console.WriteLine("...");
Console.ReadKey();
}
