ADO.NET

ADO.NET

Data provider Object ( Access )+Dataset(Manipulate )

ADO.NET = Relational Data Access Model for .net based application. Used to access datasource. Disconnected mode

# use da.Fill () return dataset ( disconnected mode)
# use ExecuteReader() to return DataReader ( connection open mode)
# use ExecuteNonQuery() for insert ,update and delete data, return # affected rows.
# use ExecuteScalar() to return the value of first row and first column

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.lbPopUp.Attributes.Add("OnClick", "Javascript: return OpenPopup()")
Me.btnSubmit.Attributes.Add("OnClick", "Javascript: return ValidateLicenseTag()")
End If
End Sub

var licenseTag =document.getElementById("<%=txtLicense.ClientID%>").value;
var amt=document.getElementById("<%=txtAmtTran.ClientID%>").value;

}
</script>

1. SqlConnection= take care of conn to Database

eg.

SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

strConnect = ConfigurationSettings.AppSettings["Conn"].ToString();
SqlConnection oConnection = new SqlConnection(strConnect);

- Required system.configuration
- In web.config,
<appSettings>
< add key="Conn" value="server=slim\express ;database=TimeManagement;uid=chian;pwd=mayyee88;"/>
</appSettings>

2. SqlCommand= Query String

SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

rdr = cmd.ExecuteReader(); //return SqlDataReader

cmd.ExecuteNonQuery(); //if query is insert, update & delete

int count = (int)cmd.ExecuteScalar(); //if query return single value ,such as max& sum

Back

3. SqlDataReader= Reading data into DataReader (1 way forward-only for reading, NOT writing)

eg.

conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read()) //trip to db each loop
{
// get the results of each column
string contact = (string)rdr["ContactName"]; //return object type, need to cast
string company = (string)rdr["CompanyName"];
string city = (string)rdr["City"];
}

4. DataSet & SqlDataAdapter (disconnected mode)
DataSet=hold data

*Fill

*Update

SelectCommand

InsertCommand

UpdateCommand

InsertCommand

DataSource -----Fill --> DataSet

<---Update---

The conn to datasource only opens when Fill and Update using SqlDataAdapter. Other time , it closed.

SqlDataAdapter=opens a connection only when required and closes it as soon as it has performed its task

objds.Fill
1. Open connection
2. Retrieve data into DataSet
3. Close connection

objds.Update
1. Open connection
2. Write changes from DataSet to data source
3. Close connection

protected DataSet GetInfo() {

string conninfo = ConfigurationSettings.AppSettings["StudentConn"];
SqlConnection conn = new SqlConnection(conninfo);

SqlCommand objcmd = new SqlCommand("SP | Ad Hoc" , conn );
objcmd.CommandType = CommandType.Text | StoredProcedure;

SqlDataAdapter objda = new SqlDataAdapter();

//SqlDataAdapter objda = new SqlDataAdapter(objcmd);
objda.SelectCommand = objcmd;

DataSet objds = new DataSet();
objda.Fill(objds , "linkedpage");
return objds;

}

Back

5. Calling SP

Create Procedure GetNameByID
(
@ID int,
@Name char(50) output

)

select @Name=ProjName from Project where ProjectID=@ID

-SqlParamter
* In
1 SqlParameters pname=new SqlParameter("@ID" , SqlDBbType . Char, 20);
2 pname.value=ddlistID;
3 cmd.Parameters.Add(pname);

* Out
1 SqlParameters pjname=new SqlParameter("@Name" , SqlDBbType . Char, 50);
2 pjname.Direction=ParameterDirection.Output
3 cmd.Parameters.Add(pjname);
4 pjname.value.

public string GetNameByID ( int ddlistID) {

SqlCommand cmd = new SqlCommand("GetNameByID", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter pname=new SqlParameter("@ID" , SqlDbType . Char, 20);
pname.value=ddlistID;
cmd.Parameters.Add(pname);

SqlParameters pjname=new SqlParameter("@Name" , SqlDbType . Char, 50);
pjname.Direction=ParameterDirection.Output //if pname is output in sp.
cmd.Parameters.Add(pjname);

Try{

conn.Open();
cmd.ExecuteNonQuery();
string strout=Convert.ToString(pjname.value);
return strout;

}
Catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
}

}

/* DataAccess method */

public DataSet GetHardwareDetail(string QAType , string top)
{
string topamt=null;
DaObj = new DataConn.DataConn("D");
conn = DaObj.GetDBConnection("D");
string spProcName = "sp_GetHardwareDetails";
command = new SqlCommand();

try
{

conn.Open();
SetCommandType(command, CommandType.StoredProcedure, spProcName, conn);

AddParamToSQLCmd(command, "DeviceType", SqlDbType.VarChar, 50,ParameterDirection.Input, QAType);

AddParamToSQLCmd(command, "TopAmount", SqlDbType.VarChar, 6, ParameterDirection.Input, top);

SetDataAdapter(command, conn);

ds = new DataSet();

da.Fill(ds, "TableName");

return ds;
}
catch (Exception e)
{
throw;
}
finally
{
conn.Close();
}

private void SetDataAdapter(SqlCommand sqlCmd, SqlConnection sqlConn)
{
da = new SqlDataAdapter();
da.SelectCommand = sqlCmd;
}

private void SetCommandType(SqlCommand sqlCmd, CommandType cmdType, String cmdText,SqlConnection conn)
{
sqlCmd.CommandType = cmdType;
sqlCmd.CommandText = cmdText;
sqlCmd.Connection = conn;
}

private void AddParamToSQLCmd( SqlCommand sqlCmd, string paramId,
SqlDbType sqlType, int paramSize, ParameterDirection paramDirection, object paramvalue)
{
if(sqlCmd ==null)
throw new ArgumentException("sqlcmd");

if (paramId==string.Empty)
throw new ArgumentOutOfRangeException("paramId");

SqlParameter newSqlParam=new SqlParameter();
newSqlParam.ParameterName=paramId;
newSqlParam.SqlDbType=sqlType;
newSqlParam.Direction=paramDirection;

if (paramSize >0)
newSqlParam.Size=paramSize;

if (paramvalue!=null)
newSqlParam.Value=paramvalue;
else
newSqlParam.Value=0;

sqlCmd.Parameters.Add(newSqlParam);

}

Back

Popolate DDL

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
dlCity.DataSource =Pop_List();
// dlCity.DataTextField="city";
// dlCity.DataValueField="city";
dlCity.DataBind();
}

}

protected ArrayList Pop_List()
{

string conninfo = ConfigurationSettings.AppSettings["NorthWindConn"];
SqlConnection conn = new SqlConnection(conninfo);

SqlCommand objcmd = new SqlCommand("select distinct city from customers", conn);
objcmd.CommandType = CommandType.Text;

conn.Open();
SqlDataReaderrdr = objcmd.ExecuteReader();

ArrayList oList = new ArrayList();
while (rdr.Read()) //trip to db each loop
{
// get the results of each column
oList.Add(rdr["city"].ToString()); //add to list

}
return oList;

/* if return dataset , using following code to add item to list

SqlDataAdapter da=new SqlDataAdapter(objcmd);

da.Fill(ds, "customerinfo");

DataTable oTable=ds.Tables["customerinfo"];

foreach(DataRow oRow in oTable.Rows)
{
oList.Add(oRow["ProjID"].ToString());
}
return oList;

}
*/

}

Back

DateTime Handling (webform vs winform)

winform : datetimepicker

Public Property _DateReceived() As String Implements Presenter.IMultiIndexView._DateReceived
Get

Dim iReceivedDate As String = Format(DateTime.Now.AddDays(1), "yyyyMMdd")
Me.dtpReceivedDate.Format = DateTimePickerFormat.Custom

If Me.dtpReceivedDate.Enabled = True Then
iReceivedDate = Me.dtpReceivedDate.Value.ToString("yyyyMMdd")
End If

Return iReceivedDate

End Get
Set(ByVal value As String)

End Set
End Property

WebForm :AJAX :Calendar Extender: Textbox

Calendar Extender Format: yyyy/MM/dd
Return : 20080326

Public Property AppBirthDate() As String
Get
Return Me.txtBirthDate.Text.Replace("/", "")
End Get
Set(ByVal value As String)

End Set
End Property

Database

Field: DateTime

Stored Procedure:

SET ANSI_WARNINGS Off (ignore warning)

Insert & Update

SET ANSI_WARNINGS On

Back