C# - using a dll with code behind 1

This is an example of using a dll file to call data from to populate a c# code behind file.


1. An example of a dll file to call data from a sql database


using System;


using System.Collections.Generic;


using System.Collections;


using System.ComponentModel;


using System.Configuration;


using System.Data.Common;


using System.Data.Odbc;


using System.Data.OleDb;


using System.Data.Sql;


using System.Data.SqlClient;


using System.Data.SqlTypes;


using System.Data;


using System.Diagnostics;


using System.Drawing;


using System.IO;


using System.Linq;


using System.Net.Mail;


using System.Net;


using System.Runtime.Serialization.Formatters.Binary;


using System.Security.Principal;


using System.Text.RegularExpressions;


using System.Text;


using Microsoft.VisualBasic;


using System.Web;


using System.Xml;


namespace TestLibrary1


{


public class DataBaseDll3
{


public DataSet GetData1(string cmdText)


{


DataSet dt = new DataSet("Test");

SqlCommand cmd = new SqlCommand();

SqlDataAdapter da = new SqlDataAdapter();

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["LIVE"].ConnectionString))


{

da.SelectCommand = con.CreateCommand();

da.SelectCommand.CommandType = CommandType.Text;

da.SelectCommand.CommandText = cmdText;

da.SelectCommand.Connection = con;



try

{
con.Open();

da.Fill(dt);

con.Close();

}

catch (Exception ec)
{ }
finally



{
con.Close();
}


}


return dt;




}

}


DON'T FORGET TO ADD THE CONNECTION STRING IN THE WEB.CONFIG FILE IN THE DLL


Then to call the result in the Code Behind..


ADD



using ;


Add the .dll file through the Reference Folder in Visual Studio


Use Browse and find the file in the bin/debug folder.


The add the follow code to call the DataSet .dll result...





DataBaseDll3 db2 = new DataBaseDll3();

Global.ds = db2.GetData1("Select GetDate();");

string test2 = Global.ds.Tables[0].Rows[0][0].ToString();

XML - Read XML data type from SQL database in C#

The code below pulls the Column with XML Data Type from the table in the SQL DATABASE
and keeps the data in XML format in c#. 


string connStr = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;

string query = " FOR XML AUTO, ROOT";

// wrap your SqlConnection and SqlCommand in using blocks...

using (SqlConnection _con = new SqlConnection(connStr))

using (SqlCommand _cmd = new SqlCommand(query, _con))



{

// setup parameter for _cmd

// _cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1;



_con.Open();

string contentsOfYourXml = (string)_cmd.ExecuteScalar();



_con.Close();

Label1.Text = contentsOfYourXml;

}

SQL Command - DataTextField for Drop Down List in Gridview

@theID int
AS SELECT
A.D_ID,
CONVERT(CHAR(5), A.D_Time, 114) as D_Time,
A.D_Cardiac_Rhythm,
b.CARD_Name,
A.D_Joules,
A.D_Drug,
D.L_Description,
A.D_Dose,
c.DOSE_NAME,
A.D_Notes
FROM DETAILS a
INNER JOIN CARDIAC_RHYTHM b
ON a.D_Cardiac_Rhythm = b.CARD_ID JOIN DOSE c
ON a.D_Dose = c.DOSE_ID JOIN LOOKUP_TEXT d
ON a.D_Drug = d.L_ID
WHERE
a.D_E_ID = @theID

ASP.NET Displaying XML in a XSLT

Quite simple this one, don't bother about using either c# or vb.net

Just use this line in the aspx code between the form tags....

asp:xml id="ID" runat="server" documentsource="tool.xml" transformsource="tool.xsl"/

IF statement in SQL Stored Procedure

A nifty little SQL code here, that checks if a value exists in a SQL table, and if it finds the value doesn't exist will insert the value....

First declare and set the variable
DECLARE @patientID VARCHAR(30)
SET @patientID = 'xxxxxxx'

next write the if statement to check if the value already exists in the table
if exists(Select * From episodes
Where patient_id=@patientID)

If it exists return and end the function
begin
return
end

Else insert the value into the table
else
begin
INSERT INTO episodes(patient_id)
values(@patientID)

End the statement

end
Go

And thats it, of course if you wish you can write this as a stored procedure. Good Luck.

Show Footer on Gridview binded from SQLDataSource

Today, is a little simple but very helpful piece of code in both c#and vb.net. This is a demonstration of how to show the footer and bind a blank row to a DataGrid that has been binded form the SQLDataSource.

Firstly, in the page load, enter the following code at the end of all other code.

In c#

int rowcount = GridView1.Rows.Count;
if (rowcount == 0)
{bindCount();
}

and in vb.net

Dim rowcount As Integer = GridView1.Rows.Count
If rowcount = 0 Then
bindCount()
End If

Then create the following void to create a false row and show the footer.


void bindCount()
{
GridView1.DataSourceID = null;
GridView1.DataSource = null;
DataTable dt = new DataTable();
string FirstC = "episode_id";
DataColumn dCol1 = new DataColumn(FirstC, typeof(System.String));
string SecondC = "code";
DataColumn dCol2 = new DataColumn(SecondC, typeof(System.String));
string ThirdC = "value";
DataColumn dCol3 = new DataColumn(ThirdC, typeof(System.String));
string FourthC = "description";
DataColumn dCol4 = new DataColumn(FourthC, typeof(System.String));
string FifthC = "theTimestamp";
DataColumn dCol5 = new DataColumn(FifthC, typeof(System.String));

dt.Columns.Add(dCol1);
dt.Columns.Add(dCol2);
dt.Columns.Add(dCol3);
dt.Columns.Add(dCol4);
dt.Columns.Add(dCol5);
for (int i = 0; i <= 0; i++) {
DataRow row1 = dt.NewRow();
row1[FirstC] = "";
row1[SecondC] = "";
row1[ThirdC] = "";
row1[FourthC] = "";
row1[FifthC] = "";
dt.Rows.Add(row1);
}
foreach (DataColumn col in dt.Columns) {
BoundField bField = new BoundField();
bField.DataField = col.ColumnName;
bField.HeaderText = col.ColumnName;
GridView1.Columns.Add(bField);
}
GridView1.DataSource = dt;
GridView1.Columns[6].Visible = false;
GridView1.Columns[7].Visible = false;
GridView1.Columns[8].Visible = false;
GridView1.Columns[9].Visible = false;
GridView1.Columns[10].Visible = false;
GridView1.DataBind();
}

and in vb.net

Private Sub bindCount()
GridView1.DataSourceID = Nothing
GridView1.DataSource = Nothing
Dim dt As New DataTable()

Dim FirstC As String = "episode_id"
Dim dCol1 As New DataColumn(FirstC, GetType(System.String))

Dim SecondC As String = "code"
Dim dCol2 As New DataColumn(SecondC, GetType(System.String))

Dim ThirdC As String = "value"
Dim dCol3 As New DataColumn(ThirdC, GetType(System.String))

Dim FourthC As String = "description"
Dim dCol4 As New DataColumn(FourthC, GetType(System.String))

Dim FifthC As String = "theTimestamp"
Dim dCol5 As New DataColumn(FifthC, GetType(System.String))

dt.Columns.Add(dCol1)
dt.Columns.Add(dCol2)
dt.Columns.Add(dCol3)
dt.Columns.Add(dCol4)
dt.Columns.Add(dCol5)

For i As Integer = 0 To 0
Dim row1 As DataRow = dt.NewRow()
row1(FirstC) = ""
row1(SecondC) = ""
row1(ThirdC) = ""
row1(FourthC) = ""
row1(FifthC) = ""
dt.Rows.Add(row1)
Next

For Each col As DataColumn In dt.Columns
Dim bField As New BoundField()
bField.DataField = col.ColumnName
bField.HeaderText = col.ColumnName
GridView1.Columns.Add(bField)
Next

GridView1.DataSource = dt
GridView1.Columns(6).Visible = False
GridView1.Columns(7).Visible = False
GridView1.Columns(8).Visible = False
GridView1.Columns(9).Visible = False
GridView1.Columns(10).Visible = False
GridView1.DataBind()

End Sub

This example has been produced on a datagrid that has 5 columns. Depending how many columns your datagrid has, increase or decrease the number of columns accordingly. This code need to take in to consideration any invisible columns you may have.

Checkbox Checked in Gridview

Just a little bit of code, which can be used in GridView to find out which row/id has been checked by a checkbox in a Gridview, this one might need some hashing about.


Sub chkStatus_OnCheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
' MsgBox("CHECKED!")
Dim checkbox As CheckBox = DirectCast(sender, CheckBox)
Dim row As GridViewRow = DirectCast(checkbox.NamingContainer, GridViewRow)
Dim UserId As Integer = CInt(GridView1.DataKeys(row.DataItemIndex).Value)
Dim UserInfo As String = ""
Dim Status As Boolean = checkbox.Checked
Dim strStatus As String = ""
UserInfo = " ("
UserInfo += GridView1.Rows(row.DataItemIndex).Cells(1).Text + " "
UserInfo += GridView1.Rows(row.DataItemIndex).Cells(4).Text
UserInfo += ")"
If Status Then
strStatus = "Approved"
Else
strStatus = "NOT Approved"
End If
' MsgBox("CheckBox1_CheckedChanged Fired for User Id = " + UserId.ToString + UserInfo + " and new status is " + strStatus + "

")
heldCodes.Text = heldCodes.Text + "," & UserId.ToString
Dim t1 As TextBox = DirectCast(sender, TextBox)
Dim t2 As String = GridView1.Rows(row.DataItemIndex).Cells(2).Text
Dim t3 As String = GridView1.Rows(row.DataItemIndex).Cells(3).Text
Dim t4 As String = GridView1.Rows(row.DataItemIndex).Cells(4).Text
'Line below works
'Dim t As TextBox = DirectCast(Me.GridView1.Rows(0).Cells(1).FindControl("lbl_quantity"), TextBox)
Dim c As CheckBox = DirectCast(Me.GridView1.Rows(0).Cells(1).FindControl("lbl_checkbox"), CheckBox)
'MsgBox("This is the TextBox text in the GridView: " & t.Text)

End Sub