This post is a simple example related to client callback functionality, which lets reader to understand how to implement client callback functionality in their application.
///////////////
ASPX
///////////////
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Client_Callback.aspx.cs" Inherits="Client_Callback" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML
1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<script type="text/javascript">
function LookUpStock()
{
var lb = document.forms[0].ListBox1;
var product = lb.options[lb.selectedIndex].text
CallServer(product, "");
}
function ReceiveServerData(rValue)
{
Results.innerText = rValue;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListBox ID="ListBox1" Runat="server"></asp:ListBox>
<br />
<br />
<button onclick="LookUpStock()">Look Up Stock</button>
<br />
<br />
Items in stock: <span ID="Results"></span>
<br />
</div>
</form>
</body>
</html>
////////////////
ASPX.CS
////////////////
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class Client_Callback : System.Web.UI.Page,
System.Web.UI.ICallbackEventHandler
{
String returnValue = string.Empty;
protected System.Collections.Specialized.ListDictionary catalog;
protected void Page_Load(object sender, EventArgs e)
{
String cbReference =
Page.ClientScript.GetCallbackEventReference(this,
"arg", "ReceiveServerData", "context");
String callbackScript;
callbackScript = "function CallServer(arg, context)" +
"{ " + cbReference + "} ;";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(),
"CallServer", callbackScript, true);
catalog = new System.Collections.Specialized.ListDictionary();
catalog.Add("monitor", 12);
catalog.Add("laptop", 10);
catalog.Add("keyboard", 23);
catalog.Add("mouse", 17);
ListBox1.DataSource = catalog;
ListBox1.DataTextField = "key";
ListBox1.DataBind();
}
public void RaiseCallbackEvent(String eventArgument)
{
if (catalog[eventArgument] == null)
{
returnValue = "-1";
}
else
{
returnValue = catalog[eventArgument].ToString();
}
}
public string GetCallbackResult()
{
return returnValue;
}
}
Friday, July 10, 2009
Generic class which helps in dynamic sql script executing
This post is a sample class, which lets the user to execute their query at run time.
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Web;
using Microsoft.VisualBasic;
///
/// Summary description for Class1
///
public class DBProcess
{
#region "Private Members"
private string connectionString = string.Empty;
private string objectQualifier = string.Empty;
private string databaseOwner = string.Empty;
#endregion
public DBProcess()
{
}
public string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}
public string ObjectQualifier
{
get { return objectQualifier; }
set { objectQualifier = value; }
}
public string DatabaseOwner
{
get { return databaseOwner; }
set { databaseOwner = value; }
}
#region ExecuteADOScript Method (first overload).
private DataSet ExecuteADOScript(string SQL) //void
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataAdapter objsqlda = null;
DataSet objds = null;
try
{
//Get the connection
connection = new SqlConnection(ConnectionString);
command = new SqlCommand(SQL, connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;
command.CommandText = SQL;
connection.Open();
objsqlda = new SqlDataAdapter();
objds = new DataSet();
objsqlda.SelectCommand = command;
objsqlda.Fill(objds);
}
finally
{
connection.Close();
command = null;
objsqlda = null;
}
return objds;
}
#endregion
#region ExecuteADOScript Method (second overload).
private DataSet ExecuteADOScript(SqlTransaction trans, string SQL)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataAdapter objsqlda = null;
DataSet objds = null;
try
{
//Get the connection
connection = trans.Connection;
command = new SqlCommand(SQL, trans.Connection);
command.Transaction = trans;
command.CommandType = CommandType.Text;
command.CommandText = SQL;
connection.Open();
objsqlda = new SqlDataAdapter();
objds = new DataSet();
objsqlda.SelectCommand = command;
objsqlda.Fill(objds);
}
finally
{
connection.Close();
command = null;
objsqlda = null;
}
return objds;
}
#endregion
#region ExecuteScript Method(first overload).
public DataSet ExecuteScript(string Script, bool UseTransactions)//string
{
string SQL = "";
string Exceptions = "";
string Delimiter = "G" + ControlChars.CrLf;
char[] char2 = Delimiter.ToCharArray();
string[] arrSQL = Script.Split(char2);//, , CompareMethod.Text);
DataSet ds = null;
if (objectQualifier != "" && (!objectQualifier.EndsWith("_")))
{
objectQualifier += "_";
}
if (databaseOwner != "" && (!databaseOwner.EndsWith(".")))
{
databaseOwner += ".";
}
if (UseTransactions)
{
SqlConnection Conn = new SqlConnection(ConnectionString);
Conn.Open();
try
{
SqlTransaction Trans = Conn.BeginTransaction();
bool IgnoreErrors;
foreach (string SQL1 in arrSQL)
{
if (SQL.Trim() != "")
{
// script dynamic substitution
SQL = SQL1.Replace("{databaseOwner}", DatabaseOwner);
SQL = SQL1.Replace("{objectQualifier}", ObjectQualifier);
IgnoreErrors = false;
if (SQL.Trim().StartsWith("{IgnoreError}"))
{
IgnoreErrors = true;
SQL = SQL1.Replace("{IgnoreError}", "");
}
try
{
ds = ExecuteADOScript(Trans, SQL);
}
catch (SqlException objException)
{
if (!IgnoreErrors)
{
Exceptions += objException.ToString() + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
}
}
}
}
if (Exceptions.Length == 0)
{
//No exceptions so go ahead and commit
Trans.Commit();
}
else
{
//Found exceptions, so rollback db
Trans.Rollback();
Exceptions += "SQL Execution failed. Database rolled back" + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
}
}
finally
{
Conn.Close();
}
}
else
{
foreach (string SQL1 in arrSQL)
{
if (SQL1.Trim() != "")
{
//script dynamic substitution
SQL = SQL1.Replace("{databaseOwner}", DatabaseOwner);
SQL = SQL.Replace("{objectQualifier}", ObjectQualifier);
try
{
ds = ExecuteADOScript(SQL);
}
catch (SqlException objException)
{
Exceptions += objException.ToString() + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
throw;
}
}
}
}
return ds;
}
#endregion
}// END CLASS DEFINITION
//////////////////////////////
Calling Client
/////////////////////////////
protected void btnGenerate_Click(object sender, EventArgs e)
{
DBProcess objDbProcess = new DBProcess();
objDbProcess.ConnectionString = txtconn.Text;
objDbProcess.DatabaseOwner = txtowner.Text;
objDbProcess.ObjectQualifier = txtoq.Text;
//txtScript.Text = objDbProcess.ExecuteScript(txtScript.Text, false);
try
{
grdresult.DataSource = objDbProcess.ExecuteScript(txtScript.Text, false);
grdresult.DataBind();
}
catch(Exception ex)
{
txtScript.Text = ex.Message;
}
}
Sometime it is require that you want execute some query on client's server but you cannot access live server every now and then. So having this type of class in your web application with a proper UI would a great help.
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Web;
using Microsoft.VisualBasic;
///
/// Summary description for Class1
///
public class DBProcess
{
#region "Private Members"
private string connectionString = string.Empty;
private string objectQualifier = string.Empty;
private string databaseOwner = string.Empty;
#endregion
public DBProcess()
{
}
public string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}
public string ObjectQualifier
{
get { return objectQualifier; }
set { objectQualifier = value; }
}
public string DatabaseOwner
{
get { return databaseOwner; }
set { databaseOwner = value; }
}
#region ExecuteADOScript Method (first overload).
private DataSet ExecuteADOScript(string SQL) //void
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataAdapter objsqlda = null;
DataSet objds = null;
try
{
//Get the connection
connection = new SqlConnection(ConnectionString);
command = new SqlCommand(SQL, connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;
command.CommandText = SQL;
connection.Open();
objsqlda = new SqlDataAdapter();
objds = new DataSet();
objsqlda.SelectCommand = command;
objsqlda.Fill(objds);
}
finally
{
connection.Close();
command = null;
objsqlda = null;
}
return objds;
}
#endregion
#region ExecuteADOScript Method (second overload).
private DataSet ExecuteADOScript(SqlTransaction trans, string SQL)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataAdapter objsqlda = null;
DataSet objds = null;
try
{
//Get the connection
connection = trans.Connection;
command = new SqlCommand(SQL, trans.Connection);
command.Transaction = trans;
command.CommandType = CommandType.Text;
command.CommandText = SQL;
connection.Open();
objsqlda = new SqlDataAdapter();
objds = new DataSet();
objsqlda.SelectCommand = command;
objsqlda.Fill(objds);
}
finally
{
connection.Close();
command = null;
objsqlda = null;
}
return objds;
}
#endregion
#region ExecuteScript Method(first overload).
public DataSet ExecuteScript(string Script, bool UseTransactions)//string
{
string SQL = "";
string Exceptions = "";
string Delimiter = "G" + ControlChars.CrLf;
char[] char2 = Delimiter.ToCharArray();
string[] arrSQL = Script.Split(char2);//, , CompareMethod.Text);
DataSet ds = null;
if (objectQualifier != "" && (!objectQualifier.EndsWith("_")))
{
objectQualifier += "_";
}
if (databaseOwner != "" && (!databaseOwner.EndsWith(".")))
{
databaseOwner += ".";
}
if (UseTransactions)
{
SqlConnection Conn = new SqlConnection(ConnectionString);
Conn.Open();
try
{
SqlTransaction Trans = Conn.BeginTransaction();
bool IgnoreErrors;
foreach (string SQL1 in arrSQL)
{
if (SQL.Trim() != "")
{
// script dynamic substitution
SQL = SQL1.Replace("{databaseOwner}", DatabaseOwner);
SQL = SQL1.Replace("{objectQualifier}", ObjectQualifier);
IgnoreErrors = false;
if (SQL.Trim().StartsWith("{IgnoreError}"))
{
IgnoreErrors = true;
SQL = SQL1.Replace("{IgnoreError}", "");
}
try
{
ds = ExecuteADOScript(Trans, SQL);
}
catch (SqlException objException)
{
if (!IgnoreErrors)
{
Exceptions += objException.ToString() + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
}
}
}
}
if (Exceptions.Length == 0)
{
//No exceptions so go ahead and commit
Trans.Commit();
}
else
{
//Found exceptions, so rollback db
Trans.Rollback();
Exceptions += "SQL Execution failed. Database rolled back" + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
}
}
finally
{
Conn.Close();
}
}
else
{
foreach (string SQL1 in arrSQL)
{
if (SQL1.Trim() != "")
{
//script dynamic substitution
SQL = SQL1.Replace("{databaseOwner}", DatabaseOwner);
SQL = SQL.Replace("{objectQualifier}", ObjectQualifier);
try
{
ds = ExecuteADOScript(SQL);
}
catch (SqlException objException)
{
Exceptions += objException.ToString() + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf + SQL + Microsoft.VisualBasic.Constants.vbCrLf + Microsoft.VisualBasic.Constants.vbCrLf;
throw;
}
}
}
}
return ds;
}
#endregion
}// END CLASS DEFINITION
//////////////////////////////
Calling Client
/////////////////////////////
protected void btnGenerate_Click(object sender, EventArgs e)
{
DBProcess objDbProcess = new DBProcess();
objDbProcess.ConnectionString = txtconn.Text;
objDbProcess.DatabaseOwner = txtowner.Text;
objDbProcess.ObjectQualifier = txtoq.Text;
//txtScript.Text = objDbProcess.ExecuteScript(txtScript.Text, false);
try
{
grdresult.DataSource = objDbProcess.ExecuteScript(txtScript.Text, false);
grdresult.DataBind();
}
catch(Exception ex)
{
txtScript.Text = ex.Message;
}
}
Sometime it is require that you want execute some query on client's server but you cannot access live server every now and then. So having this type of class in your web application with a proper UI would a great help.
Sqlserver 2005 : xquery : Basic function
Below are some of the xquery functions that you can plug in your sql script, which has some xml parsing to do. These are just simple examples but using these concepts, one can solve bigger issues
-- generating dynamic xml
declare @Emp xml
declare @TempVar table
(Code int, FirstName nvarchar(50))
insert into @TempVar values(1,'Nishant')
insert into @TempVar values(2,'Kavita')
set @Emp = (select * from @TempVar for xml path('Employee'), root('Employees'), type)
-- use of 'query' function in xquery is to fetch perticular element of list of elements from existing xml.
declare @EmpNames xml
set @EmpNames = @Emp.query('//Employees/Employee/FirstName')
select @EmpNames for xml path('EmpNames'), type
-- use of 'nodes' and 'value' function in xquery is to get values of perticular nodes from existing xml.
declare @EmpName nvarchar(10)
select @EmpName = emp.ref.value('./FirstName[1]','nvarchar(50)')
from @Emp.nodes('//Employees/Employee') emp(ref)
where emp.ref.value('./Code[1]','nvarchar(5)') = 2
select @EmpName
-- what we have done using 'value' function can also be done with 'query' but for that we need to use 'data' function in cunjuction with 'query'.
set @EmpName = cast(@Emp.query('data(//Employees/Employee/FirstName)[2]') as nvarchar(50))
select @EmpName
-- user of 'modify' function in xquery is to insert, delete or replace elements.
-- inserting xml element
set @Emp.modify('insert
select @Emp
-- deleting xml element
set @Emp.modify('delete (//Employees/Employee/Code)')
select @Emp
-- replacing xml element, it also has use of text() function and also let us know about how to use sql variable inside xquery.
declare @NewName nvarchar(50)
set @NewName = 'nish'
set @Emp.modify('replace value of (//Employees/Employee/FirstName/text())[1] with sql:variable("@NewName") ')
select @Emp
Reflection sample
reflection sample
using System;
using System.Reflection;
/////
///// Summary description for ReflectionExample
/////
public class ReflectionExample
{
public ReflectionExample()
{
//
// TODO: Add constructor logic here
//
}
public void MyMain()
{
// getting type
////////////////////////////////////////////////
// Loads an assembly using its file name.
Assembly a = Assembly.Load("MyRefExampleDLL");
// Gets the type names from the assembly.
Type[] types2 = a.GetTypes();
foreach (Type t in types2)
{
Console.WriteLine(t.FullName);
}
////////////////////////////////////////////////
// getting ConstructorInfo
/////////////////////////////////////////////////
Type type1 = typeof(System.String);
Console.WriteLine("Listing all the public constructors of the {0} type", type1);
// Constructors.
ConstructorInfo[] ci = type1.GetConstructors(BindingFlags.Public | BindingFlags.Instance);
Console.WriteLine("//Constructors");
PrintMembers(ci);
////////////////////////////////////////////////
//uses MemberInfo to list the number of members
//in the System.IO.File class and uses the System.Type.IsPublic
//property to determine the visibility of the class.
/////////////////////////////////////////////////
Console.WriteLine ("\nReflection.MemberInfo");
// Gets the Type and MemberInfo.
Type MyType =Type.GetType("System.IO.File");
MemberInfo[] Mymemberinfoarray = MyType.GetMembers();
// Gets and displays the DeclaringType method.
Console.WriteLine("\nThere are {0} members in {1}.",
Mymemberinfoarray.Length, MyType.FullName);
Console.WriteLine("{0}.", MyType.FullName);
if (MyType.IsPublic)
{
Console.WriteLine("{0} is public.", MyType.FullName);
}
/////////////////////////////////////////////////
//The following example investigates the type of the specified member.
//It performs reflection on a member of the MemberInfo class, and lists its type.
/////////////////////////////////////////////////
Console.WriteLine("Reflection.MethodInfo");
// Gets and displays the Type.
Type MyType1 = Type.GetType("System.Reflection.FieldInfo");
// Specifies the member for which you want type information.
MethodInfo Mymethodinfo = MyType1.GetMethod("GetValue");
Console.WriteLine(MyType1.FullName + "." + Mymethodinfo.Name);
// Gets and displays the MemberType property.
MemberTypes Mymembertypes = Mymethodinfo.MemberType;
if (MemberTypes.Constructor == Mymembertypes) {
Console.WriteLine("MemberType is of type All");
}
else if (MemberTypes.Custom == Mymembertypes) {
Console.WriteLine("MemberType is of type Custom");
}
else if (MemberTypes.Event == Mymembertypes) {
Console.WriteLine("MemberType is of type Event");
}
else if (MemberTypes.Field == Mymembertypes) {
Console.WriteLine("MemberType is of type Field");
}
else if (MemberTypes.Method == Mymembertypes) {
Console.WriteLine("MemberType is of type Method");
}
else if (MemberTypes.Property == Mymembertypes) {
Console.WriteLine("MemberType is of type Property");
}
else if (MemberTypes.TypeInfo == Mymembertypes) {
Console.WriteLine("MemberType is of type TypeInfo");
}
//return 0;
/////////////////////////////////////////////////
//uses all the Reflection *Info classes along with BindingFlags to list all the members
//(constructors, fields, properties, events, and methods) of the specified class, dividing
//the members into static and instance categories
////////////////////////////////////////////////
// Specifies the class.
Type type2 = typeof (System.IO.BufferedStream);
Console.WriteLine("Listing all the members (public and non public) of the {0} type", type2);
// Lists static fields first.
FieldInfo[] fi = type2.GetFields(BindingFlags.Static |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Static Fields");
PrintMembers (fi);
// Static properties.
PropertyInfo[] pi = type2.GetProperties(BindingFlags.Static |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Static Properties");
PrintMembers (pi);
// Static events.
EventInfo[] ei = type2.GetEvents(BindingFlags.Static |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Static Events");
PrintMembers (ei);
// Static methods.
MethodInfo[] mi = type2.GetMethods(BindingFlags.Static |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Static Methods");
PrintMembers (mi);
// Constructors.
ConstructorInfo[] consi = type2.GetConstructors(BindingFlags.Instance |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Constructors");
PrintMembers(consi);
// Instance fields.
fi = type2.GetFields(BindingFlags.Instance | BindingFlags.NonPublic |
BindingFlags.Public);
Console.WriteLine ("// Instance Fields");
PrintMembers (fi);
// Instance properites.
pi = type2.GetProperties(BindingFlags.Instance | BindingFlags.NonPublic |
BindingFlags.Public);
Console.WriteLine ("// Instance Properties");
PrintMembers (pi);
// Instance events.
ei = type2.GetEvents(BindingFlags.Instance | BindingFlags.NonPublic |
BindingFlags.Public);
Console.WriteLine ("// Instance Events");
PrintMembers (ei);
// Instance methods.
mi = type2.GetMethods(BindingFlags.Instance | BindingFlags.NonPublic
| BindingFlags.Public);
Console.WriteLine ("// Instance Methods");
PrintMembers (mi);
Console.WriteLine ("\r\nPress ENTER to exit.");
Console.Read();
////////////////////////////////////////////////
}
public static void PrintMembers(MemberInfo[] ms)
{
foreach (MemberInfo m in ms)
{
Console.WriteLine("{0}{1}", " ", m);
}
Console.WriteLine();
}
}
MyRefExampleDLL code
using System;
using System.Data;
using System.Configuration;
public enum Days { Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday };
public struct Book
{
public string title;
public int author ;
};
///
/// Summary description for MySimpleClass
///
public class MySimpleClass
{
public MySimpleClass()
{
//
// TODO: Add constructor logic here
//
}
public string[] Days = {"Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"};
public string name = "Nishant";
public string MyMethod(string str, int i)
{
return "MyMethod parameters: " + str + " " + i;
}
public string MyMethod(string str, int i, int j)
{
return "MyMethod parameters: " + str + " " + i + " " + j;
}
public static string MyStaticMethod(string str)
{
return "MyMethod parameters: " + str;
}
}
using System;
using System.Reflection;
/////
///// Summary description for ReflectionExample
/////
public class ReflectionExample
{
public ReflectionExample()
{
//
// TODO: Add constructor logic here
//
}
public void MyMain()
{
// getting type
////////////////////////////////////////////////
// Loads an assembly using its file name.
Assembly a = Assembly.Load("MyRefExampleDLL");
// Gets the type names from the assembly.
Type[] types2 = a.GetTypes();
foreach (Type t in types2)
{
Console.WriteLine(t.FullName);
}
////////////////////////////////////////////////
// getting ConstructorInfo
/////////////////////////////////////////////////
Type type1 = typeof(System.String);
Console.WriteLine("Listing all the public constructors of the {0} type", type1);
// Constructors.
ConstructorInfo[] ci = type1.GetConstructors(BindingFlags.Public | BindingFlags.Instance);
Console.WriteLine("//Constructors");
PrintMembers(ci);
////////////////////////////////////////////////
//uses MemberInfo to list the number of members
//in the System.IO.File class and uses the System.Type.IsPublic
//property to determine the visibility of the class.
/////////////////////////////////////////////////
Console.WriteLine ("\nReflection.MemberInfo");
// Gets the Type and MemberInfo.
Type MyType =Type.GetType("System.IO.File");
MemberInfo[] Mymemberinfoarray = MyType.GetMembers();
// Gets and displays the DeclaringType method.
Console.WriteLine("\nThere are {0} members in {1}.",
Mymemberinfoarray.Length, MyType.FullName);
Console.WriteLine("{0}.", MyType.FullName);
if (MyType.IsPublic)
{
Console.WriteLine("{0} is public.", MyType.FullName);
}
/////////////////////////////////////////////////
//The following example investigates the type of the specified member.
//It performs reflection on a member of the MemberInfo class, and lists its type.
/////////////////////////////////////////////////
Console.WriteLine("Reflection.MethodInfo");
// Gets and displays the Type.
Type MyType1 = Type.GetType("System.Reflection.FieldInfo");
// Specifies the member for which you want type information.
MethodInfo Mymethodinfo = MyType1.GetMethod("GetValue");
Console.WriteLine(MyType1.FullName + "." + Mymethodinfo.Name);
// Gets and displays the MemberType property.
MemberTypes Mymembertypes = Mymethodinfo.MemberType;
if (MemberTypes.Constructor == Mymembertypes) {
Console.WriteLine("MemberType is of type All");
}
else if (MemberTypes.Custom == Mymembertypes) {
Console.WriteLine("MemberType is of type Custom");
}
else if (MemberTypes.Event == Mymembertypes) {
Console.WriteLine("MemberType is of type Event");
}
else if (MemberTypes.Field == Mymembertypes) {
Console.WriteLine("MemberType is of type Field");
}
else if (MemberTypes.Method == Mymembertypes) {
Console.WriteLine("MemberType is of type Method");
}
else if (MemberTypes.Property == Mymembertypes) {
Console.WriteLine("MemberType is of type Property");
}
else if (MemberTypes.TypeInfo == Mymembertypes) {
Console.WriteLine("MemberType is of type TypeInfo");
}
//return 0;
/////////////////////////////////////////////////
//uses all the Reflection *Info classes along with BindingFlags to list all the members
//(constructors, fields, properties, events, and methods) of the specified class, dividing
//the members into static and instance categories
////////////////////////////////////////////////
// Specifies the class.
Type type2 = typeof (System.IO.BufferedStream);
Console.WriteLine("Listing all the members (public and non public) of the {0} type", type2);
// Lists static fields first.
FieldInfo[] fi = type2.GetFields(BindingFlags.Static |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Static Fields");
PrintMembers (fi);
// Static properties.
PropertyInfo[] pi = type2.GetProperties(BindingFlags.Static |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Static Properties");
PrintMembers (pi);
// Static events.
EventInfo[] ei = type2.GetEvents(BindingFlags.Static |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Static Events");
PrintMembers (ei);
// Static methods.
MethodInfo[] mi = type2.GetMethods(BindingFlags.Static |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Static Methods");
PrintMembers (mi);
// Constructors.
ConstructorInfo[] consi = type2.GetConstructors(BindingFlags.Instance |
BindingFlags.NonPublic | BindingFlags.Public);
Console.WriteLine ("// Constructors");
PrintMembers(consi);
// Instance fields.
fi = type2.GetFields(BindingFlags.Instance | BindingFlags.NonPublic |
BindingFlags.Public);
Console.WriteLine ("// Instance Fields");
PrintMembers (fi);
// Instance properites.
pi = type2.GetProperties(BindingFlags.Instance | BindingFlags.NonPublic |
BindingFlags.Public);
Console.WriteLine ("// Instance Properties");
PrintMembers (pi);
// Instance events.
ei = type2.GetEvents(BindingFlags.Instance | BindingFlags.NonPublic |
BindingFlags.Public);
Console.WriteLine ("// Instance Events");
PrintMembers (ei);
// Instance methods.
mi = type2.GetMethods(BindingFlags.Instance | BindingFlags.NonPublic
| BindingFlags.Public);
Console.WriteLine ("// Instance Methods");
PrintMembers (mi);
Console.WriteLine ("\r\nPress ENTER to exit.");
Console.Read();
////////////////////////////////////////////////
}
public static void PrintMembers(MemberInfo[] ms)
{
foreach (MemberInfo m in ms)
{
Console.WriteLine("{0}{1}", " ", m);
}
Console.WriteLine();
}
}
MyRefExampleDLL code
using System;
using System.Data;
using System.Configuration;
public enum Days { Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday };
public struct Book
{
public string title;
public int author ;
};
///
/// Summary description for MySimpleClass
///
public class MySimpleClass
{
public MySimpleClass()
{
//
// TODO: Add constructor logic here
//
}
public string[] Days = {"Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"};
public string name = "Nishant";
public string MyMethod(string str, int i)
{
return "MyMethod parameters: " + str + " " + i;
}
public string MyMethod(string str, int i, int j)
{
return "MyMethod parameters: " + str + " " + i + " " + j;
}
public static string MyStaticMethod(string str)
{
return "MyMethod parameters: " + str;
}
}
Thursday, July 9, 2009
Sqlserver2005: xquery : generating xml from xml
A simple example in which I have converted one xml into another
declare @emp xml
set @emp = '<employees>
<employee>
<code>1</code>
<firstname>Nishant</firstname>
<lastname>Khandhadia</lastname>
</employee>
</employees>'
declare @ModifiedEmp xml
set @ModifiedEmp = @emp.query('
element Visitors {
element Visitor {
element EntryNo { data(//Employees/Employee/Code) },
element PersonalInfo {//Employees/Employee/FirstName, //Employees/Employee/LastName }
}
}')
select @ModifiedEmp
simple but good thing to learne
declare @emp xml
set @emp = '<employees>
<employee>
<code>1</code>
<firstname>Nishant</firstname>
<lastname>Khandhadia</lastname>
</employee>
</employees>'
declare @ModifiedEmp xml
set @ModifiedEmp = @emp.query('
element Visitors {
element Visitor {
element EntryNo { data(//Employees/Employee/Code) },
element PersonalInfo {//Employees/Employee/FirstName, //Employees/Employee/LastName }
}
}')
select @ModifiedEmp
simple but good thing to learne
OOPs : Class VS structure
Class VS structure
Following are the similarities between classes and structures:-
1) Both can have constructors, methods, properties, fields, constants, enumerations, events, and event handlers.
2) Structures and classes can implement interface.
3) Both of them can have constructors with and without parameter.
4) Both can have delegates and events.
Following are the key differences between them:-
1) Structures are value types and classes are reference types.
2) Structures use stack and classes use heap.
3) Structures members cannot be declared as protected, but class members can be.
4) You cannot do inheritance in structures.
5) Structures do not require constructors while classes require.
6) Objects created from classes are terminated using Garbage collector. Structures are not destroyed using GC.
Sqlserver2005:xquery: useing namespace
This post is all about how to use namespace (xmlns) with xquery.
declare @emp xml
set @emp = '<Employees xmlns="http://www.xml.com/XSD">
<Employee>
<Code>1</Code>
<FirstName>Nishant</FirstName>
</Employee>
<Employee>
<Code>2</Code>
<FirstName>Kavita</FirstName>
</Employee>
</Employees>'
declare @totalemp int
set @totalemp = cast(cast(@emp.query('declare namespace X="http://www.xml.com/XSD";
count(/X:Employees/X:Employee/X:Code)') as varchar) as int)
select @totalemp
Sometimes simple example helps a lot.
declare @emp xml
set @emp = '<Employees xmlns="http://www.xml.com/XSD">
<Employee>
<Code>1</Code>
<FirstName>Nishant</FirstName>
</Employee>
<Employee>
<Code>2</Code>
<FirstName>Kavita</FirstName>
</Employee>
</Employees>'
declare @totalemp int
set @totalemp = cast(cast(@emp.query('declare namespace X="http://www.xml.com/XSD";
count(/X:Employees/X:Employee/X:Code)') as varchar) as int)
select @totalemp
Sometimes simple example helps a lot.
Friday, July 3, 2009
xquery sqlserver2005 - ways to insert one xml node into another xml node
This post is all about inserting one xml node in to another using sqlserver 2005 xquery.
Example
Problem
declare @parentxml xml
declare @childxml xml
set @parentxml = '<employee> <firstname>nishant</firstname> </employee>'
set @childxml = '<lastname>khandhadia</lastname>'
-- if you try this way then it will sql will gave you error
-- i.e XQuery: SQL type 'xml' is not supported in XQuery.
set @parentxml.modify( 'insert sql:variable("@childxml") as last into (/employee) ' )
select @parentxml
Solusions
-- so now let see best way outs of this
--1) Creating temptable and finding way out.
declare @str_childxml nvarchar(100)
set @str_childxml = cast(@childxml as nvarchar(100))
create table #temp (tempDetails xml)
insert into #temp values (@parentxml)
exec ( N' update #temp set tempDetails.modify ( ''insert ' + @str_childxml + ' as last into (/employee)[1]'' )' )
select @parentxml = tempDetails from #temp
drop table #temp
select @parentxml
-- 2) This option is bit simple its just string concatination, this solution you can implement
-- if you know the structure of your xml.
declare @str_childxml nvarchar(100)
set @str_childxml = cast(@childxml as nvarchar(100))
SET @parentxml = '<employee><firstname>nishant</firstname>'+@str_childxml+'</employee>'select @parentxml
--3) This option is the best option as in first option you are creating temp table so it becomes bit heavy,
--in second option you should know the structure of your xml, but this way is the best as per me.
declare @ReturnXML xml
set @ReturnXML = convert(xml, (convert(nvarchar(max), @parentxml) + convert(nvarchar(max), @childxml)))
set @ReturnXML.modify('insert /*[2] as last into (/employee)[1]')
set @ReturnXML.modify('delete /*[2]')
select @ReturnXML
isn't it great if you have multiple solutions of one problem so that you can use one of then as per your need. Have a happy coding :)
Example
Problem
declare @parentxml xml
declare @childxml xml
set @parentxml = '<employee> <firstname>nishant</firstname> </employee>'
set @childxml = '<lastname>khandhadia</lastname>'
-- if you try this way then it will sql will gave you error
-- i.e XQuery: SQL type 'xml' is not supported in XQuery.
set @parentxml.modify( 'insert sql:variable("@childxml") as last into (/employee) ' )
select @parentxml
Solusions
-- so now let see best way outs of this
--1) Creating temptable and finding way out.
declare @str_childxml nvarchar(100)
set @str_childxml = cast(@childxml as nvarchar(100))
create table #temp (tempDetails xml)
insert into #temp values (@parentxml)
exec ( N' update #temp set tempDetails.modify ( ''insert ' + @str_childxml + ' as last into (/employee)[1]'' )' )
select @parentxml = tempDetails from #temp
drop table #temp
select @parentxml
-- 2) This option is bit simple its just string concatination, this solution you can implement
-- if you know the structure of your xml.
declare @str_childxml nvarchar(100)
set @str_childxml = cast(@childxml as nvarchar(100))
SET @parentxml = '<employee><firstname>nishant</firstname>'+@str_childxml+'</employee>'select @parentxml
--3) This option is the best option as in first option you are creating temp table so it becomes bit heavy,
--in second option you should know the structure of your xml, but this way is the best as per me.
declare @ReturnXML xml
set @ReturnXML = convert(xml, (convert(nvarchar(max), @parentxml) + convert(nvarchar(max), @childxml)))
set @ReturnXML.modify('insert /*[2] as last into (/employee)[1]')
set @ReturnXML.modify('delete /*[2]')
select @ReturnXML
isn't it great if you have multiple solutions of one problem so that you can use one of then as per your need. Have a happy coding :)
Use coalesce or datalength rather appending query and executing it
1)coalesce
declare @FirstName nvarchar(30)
declare @LastName nVarchar(30)
set @FirstName = 'Nishant'
set @LastName = null
select * from employee
where FirstName = @FirstName
and LastName = coalesce(@LastName,LastName)
what this does if you pass @LastName null then it will compare column LastName = LastName,
and if you pass @LastName then it will apply and where conditions. Same as this there is one more way out for not using old way to executing dynamic SQLs.
2)datalength
declare @FirstName nvarchar(30)
declare @LastName nVarchar(30)
set @FirstName = 'Nishant'
set @LastName = null
select * from employee
where FirstName = @FirstName
and LastName = case when datalength(@LastName) > 0 then @LastName else LastName end
This is just one simple logic where I have check datalenght of @LastName, which will return 0 if the @LastName is null else it would anyway greater than 0. So by using case when you can do same thing which we did using coalesce function.
isn't it great to work smart than work hard :)
declare @FirstName nvarchar(30)
declare @LastName nVarchar(30)
set @FirstName = 'Nishant'
set @LastName = null
select * from employee
where FirstName = @FirstName
and LastName = coalesce(@LastName,LastName)
what this does if you pass @LastName null then it will compare column LastName = LastName,
and if you pass @LastName then it will apply and where conditions. Same as this there is one more way out for not using old way to executing dynamic SQLs.
2)datalength
declare @FirstName nvarchar(30)
declare @LastName nVarchar(30)
set @FirstName = 'Nishant'
set @LastName = null
select * from employee
where FirstName = @FirstName
and LastName = case when datalength(@LastName) > 0 then @LastName else LastName end
This is just one simple logic where I have check datalenght of @LastName, which will return 0 if the @LastName is null else it would anyway greater than 0. So by using case when you can do same thing which we did using coalesce function.
isn't it great to work smart than work hard :)
Subscribe to:
Posts (Atom)
