SQL:- CREATE PROCEDURE ShowSuppliers( @txt varchar(50), @Name varchar(50) output, @Company varchar (50) output, @Country varchar (50) output ) AS select @Name = ContactName, @Company = CompanyName, @Country = Country from Suppliers Where Country like "%"+ @txt +"%" GO -------------------------------------------------------------------------------------------------------- C#:- // Define the command object with stored procedure name. SqlCoommand objCommand = new Command("ShowSuppliers", objConnection); objCommand.CommandType = CommandType.StoredProcedure; // Define input Sql parameter with input value. SqlParameter Param = objCommand.Parameters.Add("@txt", SqlDbType.Varchar, 50); Param.Direction = ParameterDirection.Input; Param.Value = "US"; // Define sql stored procedure output parameters SqlParameter Param = objCommand.Parameters.Add("@Name", SqlDbType.Varchar, 50); Param.Direction = ParameterDirection.Output; SqlParameter Param = objCommand.Parameters.Add("@Company", SqlDbType.Varchar, 50); Param.Direction = ParameterDirection.Output; SqlParameter Param = objCommand.Parameters.Add("@Country", SqlDbType.Varchar, 50); Param.Direction = ParameterDirection.Output; // Execute the query objCommand.ExecuteNonQuery(); // Retrieve Sql StoredProcedure output data. Response.Write (objCommand.Parameters["@Name"].Value.ToString() + "<BR>"); Response.Write (objCommand.Parameters["@Company"].Value.ToString() + "<BR>"); Response.Write (objCommand.Parameters["@Country"].Value.ToString() + "<BR>"); ================================================================================= SQL:- CREATE PROCEDURE ShowSuppliers ( @txt varchar(50) ) AS Select CompanyName, City, Country from Suppliers Where Country like "%" + @txt + "%" -------------------------------------------------------------------------------------------------------- C#:- try{ SqlCommand objCommand = new SqlCommand("ShowSuppliers",objConnect); objCommand.CommandType = CommandType.StoredProcedure; SqlParameter Param = objCommand.Parameters.Add("@txt",SqlDbType.VarChar, 50); Param.Value = "US"; SqlDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection); while (objDataReader .Read()) { // Get data from the DataReader Console.WriteLine("CompanyName:-", (objDataReader ["CompanyName"]); Console.WriteLine("City:-", (objDataReader ["City"]); Console.WriteLine("Country :-", (objDataReader ["Country "]); } } finally { // close the reader in the face of exceptions if (objDataReader != null) { if (!objDataReader.IsClosed) objDataReader.Close(); } } MoreInfo:- http://msdn.microsoft.com/en-us/library/haa3afyz.aspx http://www.akadia.com/services/dotnet_data_reader.html http://www.informit.com/articles/article.aspx?p=26568&seqNum=5 |
ADO.NET Sample
Database Normalization- Quick Reference
The Normalization is used to organize data in a DB by eliminating redundant data and ensuring all table relationships make sense First Normal Form (1NF) First Normal Form ensures there is no repeating groups * Eliminate duplicative columns from the same table. * Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key). ============================================================================ Second Normal Form (2NF) Second normal form (2NF) further addresses the concept of eliminating Redundant Data * Meet all the requirements of the first normal form. * Remove subsets of data that apply to multiple rows of a table and place them in separate tables. * Create relationships between these new tables and their predecessors through the use of foreign keys. ============================================================================ Third Normal Form (3NF) Third normal form (3NF) further addresses the concept of eliminating Columns which are Not Dependent On the Key * Meet all the requirements of the second normal form. * Remove columns that are not dependent upon the primary key. ============================================================================ Fourth Normal Form (4NF) Finally, fourth normal form (4NF) has one additional requirement: * Meet all the requirements of the third normal form. * A relation is in 4NF if it has no multi-valued dependencies. |
SQL Table Joins- Quick Reference
EQUI-JOIN -------------- select * from emp INNER JOIN dept ON emp.deptId = dept.id; ============================================================================ INNER JOIN ---------------- select e.empId, j.salary from jobs j INNER JOIN emp e ON e.salary BETWEEN j.salary ---OR--------------------------- select e.empId, j.salary from jobs j INNER JOIN USING(Salary) **Equi Join only use to retrieve data base on '='. But Inner Join can use to retrieve data base on '=', '<', '>' **NATURAL JOIN is same to the INNER JOIN but use NATURAL JOIN keyword and which avoids duplicate columns. ============================================================================= SELF JOIN -------------- This also use INNER JOIN key word but used to retrive data from the same table. SELECT e.first_name AS 'Employee FN', e.last_name FROM employees AS e LEFT OUTER JOIN employees AS m ON e.manager =m.id ============================================================================= REFER:- http://www.codinghorror.com/blog/archives/000976.html http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ LEFT OUTER JOIN ---------------------------- Select * From emp LEFT OUTER JOIN dept ON emp.deptId = dept.Id RIGHT OUTER JOIN --------------------------- Select * From emp RIGHT OUTER JOIN dept ON emp.deptId = dept.Id FULL OUTER JOIN --------------------------- select * From emp FULL OUTER JOIN dept ON emp.eid = dept.id **Outer joins will display all columns in both tables filled with null values for culumns which doesnt retrieve the through query. More Info:- http://en.wikipedia.org/wiki/Join_(SQL) |
.NET REFLECTION
CLR loading each assembly into the appropriate application domain and controlling memmory layout of it.Assembly contains modules, module contain type and type contain members. Reflection provide object that encapsulate assemblies, modules and types. **U can use reflection to : -dynamically create an instances of a type, -bind type to an existing object or -get the type of from an existing object. ======================================================== Reflection is the feature in .Net, which enables us to get some information about object in runtime. That information contains data of the class. Also it can get the names of the methods that are inside the class and constructors of that object. ----------------------------------------------------------------------------------------------------------------- * Use Assembly to define and load assemblies, load modules that are listed in the assembly manifest, and locate a type from this assembly and create an instance of it. * Use Module to discover information such as the assembly that contains the module and the classes in the module. You can also get all global methods or other specific, nonglobal methods defined on the module. * Use ConstructorInfo to discover information such as the name, parameters, access modifiers (such as public or private), and implementation details (such as abstract or virtual) of a constructor. Use the GetConstructors or GetConstructor method of a Type to invoke a specific constructor. * Use MethodInfo to discover information such as the name, return type, parameters, access modifiers (such as public or private), and implementation details (such as abstract or virtual) of a method. Use the GetMethods or GetMethod method of a Type to invoke a specific method. * Use FieldInfo to discover information such as the name, access modifiers (such as public or private) and implementation details (such as static) of a field, and to get or set field values. * Use EventInfo to discover information such as the name, event-handler data type, custom attributes, declaring type, and reflected type of an event, and to add or remove event handlers. * Use PropertyInfo to discover information such as the name, data type, declaring type, reflected type, and read-only or writable status of a property, and to get or set property values. * Use ParameterInfo to discover information such as a parameter's name, data type, whether a parameter is an input or output parameter, and the position of the parameter in a method signature. =========================================================================== Ex:- TestDataType testObject = new TestDataType(15); Type objectType = testObject.GetType(); ConstructorInfo [] info = objectType.GetConstructors(); MethodInfo [] methods = objectType.GetMethods(); |
XML & Binary Serialization C#
The Binary serialization is fast but serialization and deserialization is language dependent while the XML serialization is platform/language independent.
FileStream fs = new FileStream("Ser.Data, FileMode.Create");
BinaryFormatter bf = new BinaryFormatter();
bf.Serialized(fs, "data");
FileStream fs = new FileStream("Ser.Data, FileMode.Open");
string data = (string) bf.Deserialized(fs);
fs.Close();
[Serialized]
[NonSerialized]
[OptionalFieled] -Use for newly added fields when try to deserialize an old instance of class
===================================================
FileStream fs = new FileStream("Ser.Data, FileMode.Create");
XmlSerializer xs = new XmlSerializer(typeOf(string));
xs.Serialize(fs, "string1");
FileStream fs = new FileStream("Ser.Data, FileMode.Open");
strig str = (string)xs.Deserialize(fs);
fs.Close();
[XmlRoot]
[XmlAttribute]
[XmlIgnore]
FileStream fs = new FileStream("Ser.Data, FileMode.Create");
BinaryFormatter bf = new BinaryFormatter();
bf.Serialized(fs, "data");
FileStream fs = new FileStream("Ser.Data, FileMode.Open");
string data = (string) bf.Deserialized(fs);
fs.Close();
[Serialized]
[NonSerialized]
[OptionalFieled] -Use for newly added fields when try to deserialize an old instance of class
===================================================
FileStream fs = new FileStream("Ser.Data, FileMode.Create");
XmlSerializer xs = new XmlSerializer(typeOf(string));
xs.Serialize(fs, "string1");
FileStream fs = new FileStream("Ser.Data, FileMode.Open");
strig str = (string)xs.Deserialize(fs);
fs.Close();
[XmlRoot]
[XmlAttribute]
[XmlIgnore]
Subscribe to:
Posts (Atom)