Code Examples - Microsoft SQL Server

USING System;
USING System.EnterpriseServices;
USING System.Reflection;
USING DDTek.SQLServer;
[assembly: ApplicationName("yourapplicationname")]
[assembly: AssemblyKeyFileAttribute(@"..\..\yourapplicationname.snk")]
namespace DistTransaction
{
/// <summary>
/// Summary description FOR Class1.
/// </summary>
public class Class1
{
/// <summary>
/// The main entry point FOR the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
SQLServerConnection Conn1;
Conn1 = NEW SQLServerConnection("host=nc-star;port=1433;
User ID=test01;Password=test01;
Database Name=Test;Enlist=true");
SQLServerConnection Conn2;
Conn2 = NEW SQLServerConnection("host=nc-star;port=1433;
User ID=test07;Password= test07;
Database Name=test;Enlist=true");
try
{
DistributedTran myDistributedTran = NEW DistributedTran();
myDistributedTran.TestDistributedTransaction(Conn1, Conn2);
Console.WriteLine("Success!!");
}
catch (Exception e)
{
System.Console.WriteLine("Error returned: " + e.Message);
}
}
}
/// <summary>
/// TO USE distributed transactions IN .NET, we need a ServicedComponent
/// derived class WITH TRANSACTION attribute declared AS "Required".
/// </summary>
[TRANSACTION(TransactionOption.Required) ]
public class DistributedTran : ServicedComponent
{
/// <summary>
/// This method executes two SQL statements.
/// IF BOTH are successful, BOTH are committed BY DTC after the
/// method finishes. However, IF an exception IS thrown, BOTH will be
/// rolled back BY DTC.
/// </summary>
[AutoComplete]
public void TestDistributedTransaction(SQLServerConnection Conn1,
SQLServerConnection Conn2)
{
// The following INSERT statement goes TO the FIRST server, orca.
// This INSERT statement does NOT produce any errors.
string DBCmdSql1 = "Insert into emp VALUES
(16,'HAYES','ADMIN',6,'17-NOV-2002',18000,
NULL,4)";
string DBCmdSql2 = "Delete from emp WHERE sal > 100000";
try
{
Conn1.OPEN();
Conn2.OPEN();
Console.WriteLine ("Connection successful!");
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
RETURN;
}
SQLServerCommand DBCmd1 = NEW SQLServerCommand(DBCmdSql1, Conn1);
SQLServerCommand DBCmd2 = NEW SQLServerCommand(DBCmdSql2, Conn2);
DBCmd1.ExecuteNonQuery();
DBCmd2.ExecuteNonQuery();
Conn1.Close();
Conn2.Close();
Console.WriteLine("Success!! ");
}
}
}



CREATE TABLE emp (
empno INT PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr INT NULL,
hiredate DATETIME,
sal NUMERIC(7,2),
comm NUMERIC(7,2) NULL,
dept INT)
BEGIN
INSERT INTO emp VALUES
    (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)
INSERT INTO emp VALUES
    (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)
INSERT INTO emp VALUES
    (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)
INSERT INTO emp VALUES
    (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)
INSERT INTO emp VALUES
    (5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)
INSERT INTO emp VALUES
    (6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)
INSERT INTO emp VALUES
    (7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)
INSERT INTO emp VALUES
    (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)
INSERT INTO emp VALUES
    (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)
INSERT INTO emp VALUES
    (10,'FILLMORE','MANAGER',9,'08-09-1994',56000,NULL,2)
INSERT INTO emp VALUES
    (11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2)
INSERT INTO emp VALUES
    (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)
INSERT INTO emp VALUES
    (13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)
INSERT INTO emp VALUES
    (14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)
END
CREATE TABLE dept (
deptno INT NOT NULL,
dname VARCHAR(14),
loc VARCHAR(13))
BEGIN
INSERT INTO dept VALUES (1,'ACCOUNTING','ST LOUIS')
INSERT INTO dept VALUES (2,'RESEARCH','NEW YORK')
INSERT INTO dept VALUES (3,'SALES','ATLANTA')
INSERT INTO dept VALUES (4, 'OPERATIONS','SEATTLE')
END



SQLServerConnection Conn;
Conn = NEW SQLServerConnection("host=nc-star;port=1433;
User ID=test01;Password=test01; Database Name=Test");
try
{
Conn.OPEN();
}
catch (SQLServerException ex)
{
// Connection failed
Console.WriteLine(ex.Message);
RETURN;
}
string[] DropTableSQL = {"drop table emp", "drop table dept"};
FOR (INT x=0; x<=1; x++)
{
try
{
// DROP the TABLES, don't care if they don't exist
SQLServerCommand DBCmd = NEW SQLServerCommand(DropTableSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch (SQLServerException ex)
{
}
// CREATE the TABLES
string CreateEmpTableSQL = "CREATE TABLE emp
(empno INT PRIMARY KEY NOT NULL,"
+"ename VARCHAR(10) NOT NULL,"
+"job VARCHAR(9) NOT NULL,"
+"mgr INT,"
+"hiredate DATETIME NOT NULL,"
+"sal NUMERIC(7,2) NOT NULL,"
+"comm NUMERIC(7,2),"
+"dept INT NOT NULL)";
string CreateDeptTableSQL = "CREATE TABLE dept ("
+"deptno INT NOT NULL,"
+"dname VARCHAR(14),"
+"loc VARCHAR(13))";
try
{
SQLServerCommand DBCmd = NEW SQLServerCommand(CreateEmpTableSQL, Conn);
DBCmd.ExecuteNonQuery();
DBCmd.CommandText = CreateDeptTableSQL;
DBCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//CREATE TABLES failed
Console.WriteLine (ex.Message);
RETURN;
}
// Now INSERT the records
string[] InsertEmpRecordsSQL = {
     "insert into emp values
        (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)",
     "insert into emp values
        (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)",
     "insert into emp values
        (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)",
     "insert into emp values
        (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)",
     "insert into emp values
        (5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)",
     "insert into emp values
        (6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)",
     "insert into emp values
        (7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)",
     "insert into emp values
        (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)",
     "insert into emp values
        (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)",
"insert into emp values
    (10,'FILLMORE','MANAGER',9,'08-09-1994',56000, NULL,2)",
     "insert into emp values
        (11,'ADAMS','ENGINEER',10,'03-15-1996',34000, NULL,2)",
     "insert into emp values
        (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)",
     "insert into emp values
        (13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)",
     "insert into emp values
        (14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)"};
string[] InsertDeptRecordsSQL = {
     "insert into dept values (1,'ACCOUNTING','ST LOUIS')",
     "insert into dept values (2,'RESEARCH','NEW YORK')",
     "insert into dept values (3,'SALES','ATLANTA')",
     "insert into dept values (4, 'OPERATIONS','SEATTLE')"};
// INSERT dept TABLE records FIRST
FOR (INT x = 0; x<InsertDeptRecordsSQL.LENGTH; x++)
{
try
{
SQLServerCommand DBCmd =
    NEW SQLServerCommand(InsertDeptRecordsSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine (ex.Message);
RETURN;
}
}
// Now the emp TABLE records
FOR (INT x = 0; x<InsertEmpRecordsSQL.LENGTH; x++)
{
try
{
SQLServerCommand DBCmd =
    NEW SQLServerCommand(InsertEmpRecordsSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine (ex.Message);
RETURN;
}
}
Console.WriteLine ("Tables created Successfully!");
// Close the connection
Conn.Close();


OPEN connection TO SQL Server DATABASE
SQLServerConnection Conn;
try
{
Conn = NEW SQLServerConnection("host=nc-star;port=1433;
User ID=test01;Password=test01; Database Name=Test");
Conn.OPEN();
Console.WriteLine ("Connection successful!");
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
RETURN;
}
try
{
// CREATE a SQL command
string strSQL = "SELECT ename FROM emp WHERE sal>50000";
SQLServerCommand DBCmd = NEW SQLServerCommand(strSQL, Conn);
SQLServerDataReader myDataReader;
myDataReader = DBCmd.ExecuteReader();
while (myDataReader.READ())
{
Console.WriteLine("High salaries: " + myDataReader["ename"].ToString());
}
myDataReader.Close();
// Close the connection
Conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
RETURN;
}



SQLServerConnection Conn;
Conn = NEW SQLServerConnection("host=nc-star;port=1433; User ID=test01;
Password=test01;Database Name=Test");
try
{
Conn.OPEN();
Console.WriteLine ("Connection successful!");
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
RETURN;
}
SQLServerCommand DBCmd = NEW SQLServerCommand();
SQLServerTransaction DBTxn = NULL;
try
{
DBTxn = Conn.BeginTransaction();
// SET the Connection property OF the Command object
DBCmd.Connection = Conn;
// SET the text OF the Command TO the INSERT statement
DBCmd.CommandText = "insert into emp VALUES
(16,'HAYES','ADMIN',6,'17-APR-2002',18000,NULL,4)";
// SET the TRANSACTION property OF the Command object
DBCmd.TRANSACTION = DBTxn;
// EXECUTE the statement WITH ExecuteNonQuery, because we are NOT
// returning results
DBCmd.ExecuteNonQuery();
// Now commit the TRANSACTION
DBTxn.Commit();
// Display any exceptions
Console.WriteLine ("Transaction Committed!");
}
catch (Exception ex)
{
// Display any exceptions
Console.WriteLine (ex.Message);
// IF anything failed after the connection was opened, roll back the
// TRANSACTION
IF (DBTxn != NULL)
{
DBTxn.ROLLBACK();
}
}
// Close the connection
Conn.Close();



SQLServerConnection Conn;
Conn = NEW SQLServerConnection("host=nc-star; port=4100;User ID=test01;
Password=test01;Database Name=Test ");
try
{
Conn.OPEN();
Console.WriteLine ("Connection successful!");
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
RETURN;
}
SQLServerDataAdapter myDataAdapter = NEW SQLServerDataAdapter();
SQLServerCommand DBCmd = NEW SQLServerCommand("select * from emp",Conn);
myDataAdapter.SelectCommand = DBCmd;
// SET up the CommandBuilder
SQLServerCommandBuilder CommBuild =
    NEW SQLServerCommandBuilder(myDataAdapter);
DataSet myDataSet = NEW DataSet();
try
{
myDataAdapter.Fill(myDataSet);
// Now CHANGE the salary OF the FIRST employee
DataRow myRow;
myRow = myDataSet.TABLES["Table"].ROWS[0];
myRow["sal"] = 95000;
// Tell the DataAdapter TO resync WITH the SQL Server server.
// WITHOUT the CommandBuilder, this line would fail.
myDataAdapter.UPDATE(myDataSet);
Console.WriteLine ("Update with CommandBuilder Successful!");
}
catch (Exception ex)
{
// Display any exceptions
Console.WriteLine (ex.Message);
}
// Close the connection
Conn.Close();



SQLServerConnection Conn =
NEW SQLServerConnection("host=nc-star;port=4100;User ID=test01;
        Password=test01;Database Name=Test");
try
{
string selectText = "select sal, job, empno from emp";
string updateText = "update emp set sal = ?, job = ? where empno = ?";
SQLServerDataAdapter adapter = NEW SQLServerDataAdapter(selectText, Conn);
SQLServerCommand updateCommand = NEW SQLServerCommand(updateText, Conn);
adapter.UpdateCommand = updateCommand;
updateCommand.Parameters.ADD("@sal", SQLServerDbType.INT, 15, "SAL");
updateCommand.Parameters.ADD("@job", SQLServerDbType.VARCHAR, 9, "JOB");
updateCommand.Parameters.ADD("@empno", SQLServerDbType.INT, 15, "empno");
DataSet myDataSet = NEW DataSet("emp");
adapter.Fill(myDataSet, "emp");
// Give employee NUMBER 11 a promotion AND a raise
DataRow changeRow = myDataSet.TABLES["emp"].ROWS[11];
changeRow["sal"] = "35000";
changeRow["job"] = "MANAGER";
// Send back TO DATABASE
adapter.UPDATE(myDataSet, "emp");
myDataSet.Dispose();
}
catch (Exception ex)
{
// Display any exceptions
Console.WriteLine (ex.Message);
}
Console.WriteLine("DataSet Updated Successfully!");
// Close the connection
Conn.Close();



// OPEN connection TO SQL Server DATABASE
SQLServerConnection Conn;
Conn = NEW SQLServerConnection("host=nc-star;port=4100;User ID=test01;
Password=test01;Database Name=Test");
try
{
Conn.OPEN();
Console.WriteLine ("Connection successful!");
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
RETURN;
}
string spCreate = "CREATE PROCEDURE GetEmpSalary(@empno int,@sal
numeric(7,2) output)AS SELECT @sal = sal from emp where empno = @empno";
try
{
SQLServerCommand DBCmd=NEW SQLServerCommand(spCreate, Conn);
DBCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//CREATE PROCEDURE failed
Console.WriteLine (ex.Message);
RETURN;
}
Console.WriteLine ("Procedure Created Successfully!");



// OPEN connection TO SQL Server DATABASE
SQLServerConnection Conn;
try
{
Conn = NEW SQLServerConnection("host=nc-star;port=4100;User ID=test01;
Password=test01;Database Name=Test");
Conn.OPEN();
Console.WriteLine ("Connection successful!");
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
RETURN;
}
// Make a command object FOR the stored PROCEDURE
// You must SET the CommandType OF the Command object
// TO StoredProcedure
SQLServerCommand DBCmd = NEW SQLServerCommand("GetEmpSalary",Conn);
DBCmd.CommandType = CommandType.StoredProcedure;
// The stored PROCEDURE expects one INPUT AND one output parameter
// Define the parameters FOR the stored PROCEDURE
// We don't need to specify the direction of the parameter, since the
default is INPUT
DBCmd.Parameters.Add("@empno", SQLServerDbType.Int, 10).Value = 5;
// Output parameter
DBCmd.Parameters.Add("@sal", SQLServerDbType.Numeric, 10).Direction =
ParameterDirection.Output;
SQLServerDataReader myDataReader;
try
{
myDataReader = DBCmd.ExecuteReader();
myDataReader.Close();
}
catch (Exception ex)
{
// Display any exceptions
Console.WriteLine (ex.Message);
}
Console.WriteLine("Procedure Executed Successfully!");
// Close the connection
Conn.Close();



// Open connection to SQL Server database
SQLServerConnection Conn;
Conn = new SQLServerConnection("host=nc-star;port=4100;User ID=test01;
Password=test01;Database Name=Test");
try
{
Conn.Open();
Console.WriteLine ("Connection successful!");
}
catch (Exception ex)
{
// Connection failed
Console.WriteLine(ex.Message);
return;
}
// Make a command object
SQLServerCommand salCmd = new SQLServerCommand("select count(sal) from emp
where sal>50000",Conn);
try
{
int count = (int)salCmd.ExecuteScalar();
Console.WriteLine("Count of Salaries >$50,000 : "
+ Convert.ToString(count));
}
catch (Exception ex)
{
// Display any exceptions
Console.WriteLine(ex.Message);
}
// Close the connection
Conn.Close();



// Define an event handler
public void myHandler(object sender, SQLServerInfoMessageEventArgs e)
{
// Display any warnings
Console.WriteLine ("Warning Returned: " + e.Message);



// Define an event handler
public void myHandler(object sender, SQLServerInfoMessageEventArgs e)
{
// Display any warnings
Console.WriteLine ("Warning Returned: " + e.Message);
}
Add the following code to a method and call it:
SQLServerConnection Conn;
Conn = new SQLServerConnection("host=nc-star;port=4100;User ID=test01;
Password=test01;Database Name=Test");
SQLServerCommand DBCmd = new SQLServerCommand
("print 'This IS a Warning.'",Conn);
SQLServerDataReader myDataReader;
try
{
Conn.InfoMessage += new SQLServerInfoMessageEventHandler(myHandler);
Conn.Open();
myDataReader = DBCmd.ExecuteReader();
// This will throw a SQLServerInfoMessageEvent as the print
// statement generates a warning.
}
catch (Exception ex)
{
// Display any exceptions in a messagebox
MessageBox.Show (ex.Message);
}
// Close the connection
Conn.Close();