public static DataTable LogonUser(string userName,string password)
{
SqlConnection cn = null;
try
{
cn = new SqlConnection(ConfigurationSettings.AppSettings["cn"]);
cn.Open();
userName = userName.Replace("'","''");
password = password.Replace("'","''");
SqlDataAdapter da = new SqlDataAdapter("SELECT KullaniciAdi, Sifre, KullaniciId, Adi_SoyAdi, Unvan, Email, Aciklama, DilId, CreDate, ModDate, IsOnay, IsAktif, ModUser, CreUser FROM tGuvKullanici where KullaniciAdi='"+ userName + "' and Sifre='"+ password + "'",cn);
System.Data.DataSet ds = new DataSet();
da.Fill(ds);
SqlCommandBuilder builder = new SqlCommandBuilder(da);
builder.RefreshSchema();
return ds.Tables[0];
}
catch(Exception ex)
{
return new DataTable();
}
finally
{
if (cn!=null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
public void QuoteSuffix_DeleteCommand_Generated ()
{
SqlCommand cmd = null;
try {
string selectQuery = "select id, lname from employee where id = 3";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
SqlCommandBuilder cb = new SqlCommandBuilder (da);
cmd = cb.GetDeleteCommand ();
Assert.AreEqual ("]", cb.QuoteSuffix, "#1");
try {
cb.QuoteSuffix = "\"";
Assert.Fail ("#2");
} catch (InvalidOperationException ex) {
// The QuotePrefix and QuoteSuffix properties
// cannot be changed once an Insert, Update, or
// Delete command has been generated
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
Assert.IsNull (ex.InnerException, "#4");
Assert.IsNotNull (ex.Message, "#5");
}
Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
cb.RefreshSchema ();
cb.QuoteSuffix = "\"";
} finally {
if (cmd != null)
cmd.Dispose ();
}
}
public void GetInsertCommand1 ()
{
SqlCommand cmd = null;
try {
string selectQuery = "select id, fname, lname " +
"from employee where id = 1";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
Assert.AreEqual (1, ds.Tables.Count);
SqlCommandBuilder cb;
cb = new SqlCommandBuilder (da);
cmd = cb.GetInsertCommand ();
Assert.AreEqual ("INSERT INTO [employee] ([id], " +
"[fname], [lname]) VALUES (@p1, @p2, @p3)",
cmd.CommandText, "#A1");
Assert.AreSame (conn, cmd.Connection, "#A2");
AssertInsertParameters (cmd, false, "#A3:");
Assert.AreSame (cmd, cb.GetInsertCommand (), "#A4");
cb.RefreshSchema ();
cb.QuotePrefix = "\"";
cmd = cb.GetInsertCommand ();
Assert.AreEqual ("INSERT INTO \"employee] (\"id], " +
"\"fname], \"lname]) VALUES (@p1, @p2, @p3)",
cmd.CommandText, "#B1");
Assert.AreSame (conn, cmd.Connection, "#B2");
AssertInsertParameters (cmd, false, "#B3:");
Assert.AreSame (cmd, cb.GetInsertCommand (), "#B4");
cb.RefreshSchema ();
cb.QuoteSuffix = "\"";
cmd = cb.GetInsertCommand ();
Assert.AreEqual ("INSERT INTO \"employee\" (\"id\", "
+ "\"fname\", \"lname\") VALUES (@p1, @p2, @p3)",
cmd.CommandText, "#C1");
Assert.AreSame (conn, cmd.Connection, "#C2");
AssertInsertParameters (cmd, false, "#C3");
Assert.AreSame (cmd, cb.GetInsertCommand (), "#C4");
} finally {
if (cmd != null)
cmd.Dispose ();
}
}
[Test] // GetDeleteCommand ()
public void GetDeleteCommand1 ()
{
SqlCommand cmd = null;
try {
string selectQuery = "select id, fname, lname, " +
"id+2 as next_id from employee where " +
"id = 3 and lname = 'A' and fname = 'B'";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
Assert.AreEqual (1, ds.Tables.Count);
SqlCommandBuilder cb = new SqlCommandBuilder (da);
cmd = cb.GetDeleteCommand ();
Assert.AreEqual ("DELETE FROM [employee] WHERE " +
"(([id] = @p1) AND ([fname] = @p2) AND " +
"((@p3 = 1 AND [lname] IS NULL) OR " +
"([lname] = @p4)))", cmd.CommandText, "#A1");
Assert.AreSame (conn, cmd.Connection, "#A2");
AssertDeleteParameters (cmd, false, "#A3:");
Assert.AreSame (cmd, cb.GetDeleteCommand (), "#A4");
cb.RefreshSchema ();
cb.QuotePrefix = "\"";
cmd = cb.GetDeleteCommand ();
Assert.AreEqual ("DELETE FROM \"employee] WHERE " +
"((\"id] = @p1) AND (\"fname] = @p2) AND " +
"((@p3 = 1 AND \"lname] IS NULL) OR " +
"(\"lname] = @p4)))", cmd.CommandText, "#B1");
Assert.AreSame (conn, cmd.Connection, "#B2");
AssertDeleteParameters (cmd, false, "#B3:");
Assert.AreSame (cmd, cb.GetDeleteCommand (), "#B4");
cb.RefreshSchema ();
cb.QuoteSuffix = "\"";
cmd = cb.GetDeleteCommand ();
Assert.AreEqual ("DELETE FROM \"employee\" WHERE " +
"((\"id\" = @p1) AND (\"fname\" = @p2) AND " +
"((@p3 = 1 AND \"lname\" IS NULL) OR " +
"(\"lname\" = @p4)))", cmd.CommandText, "#C1");
Assert.AreSame (conn, cmd.Connection, "#C2");
AssertDeleteParameters (cmd, false, "#C3:");
Assert.AreSame (cmd, cb.GetDeleteCommand (), "#C4");
} finally {
if (cmd != null)
cmd.Dispose ();
}
}
[Test] // GetUpdateCommand ()
public void GetUpdateCommand1 ()
{
SqlCommand cmd = null;
try {
string selectQuery = "select id, fname, lname, " +
"id+1 as next_id from employee where " +
"id = 3 and lname = 'A' and fname = 'B'";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
Assert.AreEqual (1, ds.Tables.Count);
SqlCommandBuilder cb = new SqlCommandBuilder (da);
cmd = cb.GetUpdateCommand ();
#if NET_2_0
Assert.AreEqual ("UPDATE [employee] SET [id] = @p1, " +
"[fname] = @p2, [lname] = @p3 WHERE (([id] = @p4) " +
"AND ([fname] = @p5) AND ((@p6 = 1 " +
"AND [lname] IS NULL) OR ([lname] = @p7)))",
cmd.CommandText, "#A1");
#else
Assert.AreEqual ("UPDATE employee SET id = @p1 , " +
"fname = @p2 , lname = @p3 WHERE ( (id = @p4) " +
"AND ((@p5 = 1 AND fname IS NULL) OR " +
"(fname = @p6)) AND ((@p7 = 1 AND " +
"lname IS NULL) OR (lname = @p8)) )",
cmd.CommandText, "#A1");
#endif
Assert.AreSame (conn, cmd.Connection, "#A2");
AssertUpdateParameters (cmd, false, "#A3:");
Assert.AreSame (cmd, cb.GetUpdateCommand (), "#A4");
cb.RefreshSchema ();
cb.QuotePrefix = "\"";
cmd = cb.GetUpdateCommand ();
#if NET_2_0
Assert.AreEqual ("UPDATE \"employee] SET \"id] = @p1, " +
"\"fname] = @p2, \"lname] = @p3 WHERE ((\"id] = @p4) " +
"AND (\"fname] = @p5) AND ((@p6 = 1 " +
"AND \"lname] IS NULL) OR (\"lname] = @p7)))",
cmd.CommandText, "#B1");
#else
Assert.AreEqual ("UPDATE \"employee SET \"id = @p1 , " +
"\"fname = @p2 , \"lname = @p3 WHERE ( (\"id = @p4) " +
"AND ((@p5 = 1 AND \"fname IS NULL) OR " +
"(\"fname = @p6)) AND ((@p7 = 1 AND " +
"\"lname IS NULL) OR (\"lname = @p8)) )",
cmd.CommandText, "#B1");
#endif
Assert.AreSame (conn, cmd.Connection, "#B2");
AssertUpdateParameters (cmd, false, "#B3:");
Assert.AreSame (cmd, cb.GetUpdateCommand (), "#B4");
cb.RefreshSchema ();
#if NET_2_0
cb.QuoteSuffix = "\"";
#else
cb.QuoteSuffix = "´";
#endif
cmd = cb.GetUpdateCommand ();
#if NET_2_0
Assert.AreEqual ("UPDATE \"employee\" SET \"id\" = @p1, " +
"\"fname\" = @p2, \"lname\" = @p3 WHERE ((\"id\" = @p4) " +
"AND (\"fname\" = @p5) AND ((@p6 = 1 " +
"AND \"lname\" IS NULL) OR (\"lname\" = @p7)))",
cmd.CommandText, "#C1");
#else
Assert.AreEqual ("UPDATE \"employee´ SET \"id´ = @p1 , " +
"\"fname´ = @p2 , \"lname´ = @p3 WHERE ( (\"id´ = @p4) " +
"AND ((@p5 = 1 AND \"fname´ IS NULL) OR " +
"(\"fname´ = @p6)) AND ((@p7 = 1 AND " +
"\"lname´ IS NULL) OR (\"lname´ = @p8)) )",
cmd.CommandText, "#C1");
#endif
Assert.AreSame (conn, cmd.Connection, "#C2");
AssertUpdateParameters (cmd, false, "#C3:");
Assert.AreSame (cmd, cb.GetUpdateCommand (), "#C4");
} finally {
if (cmd != null)
cmd.Dispose ();
}
}
/*Esta función es la encargada de en MODO DESCONECTADO, cargar los vendedores de la BD
*y devolvernos un DataView que manipularemos a posteriori*/
protected DataView cargaVistaVendedores()
{
SqlConnection conexion;
DataSet dataSet;
String sentencia;
SqlDataAdapter dataAdapterVendedor;
DataView dataViewVendedor = new DataView();
SqlCommandBuilder comandBuilderVendedor;
string cadenaConexion = System.Configuration.ConfigurationManager.ConnectionStrings["DB_FACTURACION"].ConnectionString;
conexion = new SqlConnection(cadenaConexion);
sentencia = "SELECT COD_VEN,Nombre,Telefono FROM TB_Vendedor";
dataAdapterVendedor = new System.Data.SqlClient.SqlDataAdapter(sentencia, conexion);
dataAdapterVendedor.TableMappings.Add("Table", "Vendedor");
comandBuilderVendedor = new System.Data.SqlClient.SqlCommandBuilder(dataAdapterVendedor);
comandBuilderVendedor.QuotePrefix = "[";
comandBuilderVendedor.QuoteSuffix = "]";
comandBuilderVendedor.RefreshSchema();
dataSet = new System.Data.DataSet();
try
{
conexion.Open();
dataAdapterVendedor.Fill(dataSet);
conexion.Close();
dataViewVendedor.Table = dataSet.Tables["Vendedor"];
}
catch (SqlException)
{
lblError.Text = "Los datos no se han cargado correctamente";
}
return dataViewVendedor;
}
/*Esta función es la encargada de en MODO DESCONECTADO, cargar el Stock de la BD
*y devolvernos un DataView que manipularemos a posteriori*/
protected DataView cargaVistaStock()
{
limpiarLablesError();
SqlConnection conexion;
DataSet dataSet;
String sentencia;
SqlDataAdapter dataAdapterStock;
DataView dataViewStock = new DataView();
SqlCommandBuilder comandBuilderStock;
string cadenaConexion = System.Configuration.ConfigurationManager.ConnectionStrings["DB_FACTURACION"].ConnectionString;
conexion = new SqlConnection(cadenaConexion);
sentencia = "SELECT COD_PRO,Casa,Nombre,Descripcion,Familia,Precio,Imagen,Cantidad,CantidadMin FROM TB_Stock";
dataAdapterStock = new System.Data.SqlClient.SqlDataAdapter(sentencia, conexion);
dataAdapterStock.TableMappings.Add("Table", "Stock");
comandBuilderStock = new System.Data.SqlClient.SqlCommandBuilder(dataAdapterStock);
comandBuilderStock.QuotePrefix = "[";
comandBuilderStock.QuoteSuffix = "]";
comandBuilderStock.RefreshSchema();
dataSet = new System.Data.DataSet();
try
{
conexion.Open();
dataAdapterStock.Fill(dataSet);
conexion.Close();
dataViewStock.Table = dataSet.Tables["Stock"];
}
catch (SqlException)
{
lblError.Text = "Los datos no se han cargado correctamente";
}
return dataViewStock;
}