C# SqlCommandBuilder.RefreshSchema方法代码示例

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