Tag Archives: C#

An example of a project in c # where I use different ways to acquire data from a MSSQL database, using a “dades.xsd” and with direct connections.
a little to see the different ways of connection to the database:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
// references added :
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace comandes_Web
{
	public partial class _Default : System.Web.UI.Page
	{
		private string cadena;
		protected void Page_Load(object sender, EventArgs e)
	{
	}
	 
	protected void btacceptar_Click(object sender, EventArgs e)
	{
		// with this line we take configuration of  "web.config" file at value:  ConnectionStrings
		cadena = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString ;
		 
		//using  dades.xsd
		dades.tenUsersDataTable res1;
		res1 = new dades.UsersDataTable();
		dadesTableAdapters.UsersTableAdapter TenTA;
		TenTA = new dadesTableAdapters.UsersTableAdapter();
		TenTA.Fill(res1);
		 
		//ussing direct metods
		SqlDataAdapter daSql;
		SqlConnection conSql;
		DataSet res2;
		DataTable res3;
		conSql = new SqlConnection(cadena); // using the connection string   dades.xsd
		daSql = new SqlDataAdapter("SELECT  Codigo, Password FROM Users", conSql);
		//with dataset
		res2 = new DataSet() ;
		daSql.Fill(res2, "Users");
		//with datatable
		res3 = new DataTable();
		daSql.Fill(res3);
		 
		//see the number or files 3 diferent ways:
		lblresul.Text = res1.Count.ToString();
		lblresul.Text = res2.Tables["Users"].Rows.Count.ToString() ;
		lblresul.Text = res3.Rows.Count.ToString() ;
		 
		//see rows content 3 diferent ways:<
		lblresul.Text = res1[0]["Codigo"].ToString() + " - " + res1[0]["Password"].ToString();
		lblresul.Text = res2.Tables["Users"].Rows[0]["Codigo"].ToString() + " - " + res2.Tables["Users"].Rows[0]["Password"].ToString();
		lblresul.Text = res3.Rows[0]["Codigo"].ToString() + " - " + res3.Rows [0]["Password"].ToString();
		 
		//more complex!!
		res3.Clear();
		res3 = res2.Tables["Users"];
		lblresul.Text = res3.Rows[0]["Codigo"].ToString() + " - " + res3.Rows[0]["Password"].ToString();
		 
		int resultat;
		//call scalar query with dades.xsd
		dadesTableAdapters.QueriesTableAdapter queryTA;
		queryTA = new dadesTableAdapters.QueriesTableAdapter();
		resultat= (int) queryTA.ComprovarUsuari("user", "password");
		lblresul.Text = resultat.ToString ();
		 
		//a query only returns one value an can do INSERTS UPDATES AND DELETES
		 
		//call a query (direct metoth)  ( no parameters)
		SqlCommand querysql;
		string resuls;
		querysql = new SqlCommand("SELECT Codigo,Password FROM Users ", conSql);
		conSql.Open();
		// 2 ways to do ( one value  , first row, first column )
		resuls = (string)querysql.ExecuteScalar();
		resuls = querysql.ExecuteScalar().ToString();
		lblresul.Text = resuls ;
		//execute query with  datareader
		SqlDataReader sqldatar;
		sqldatar = querysql.ExecuteReader();
		//loop to  see files content
		while (sqldatar.Read())
		{
			lblresul.Text = sqldatar[0].ToString ();
			lblresul.Text = sqldatar["Codigo"].ToString();
		}
		sqldatar.Close();
		sqldatar.Dispose();
		 
		//execute query , only return files affected
		int files;
		files = querysql.ExecuteNonQuery();
		lblresul.Text = files.ToString();
		conSql.Close();
	 
	}
}