Function c# Load tables from sql 2000 -2005

public void Load_Tables()
{

dataSetPinakes.Tables[0].Clear();
System.Data.SqlClient.SqlCommand sqlcmdselect = new SqlCommand();
sqlcmdselect.CommandText = “select name, ‘0′ as see  from sysobjects where xtype=’U'” ;
sqlcmdselect.Connection = MSSqlcon;
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter_kos = new SqlDataAdapter();
sqlDataAdapter_kos.SelectCommand = sqlcmdselect;

try
{
sqlDataAdapter_kos.Fill(dataSetPinakes.Tables[0]);

DataRow[] row_select = dataSetPinakes.Tables[0].Select();
cblPinakes.Items.Clear();
for (int bb = 0; bb < row_select.Length; bb++)
{

object obj = row_select[bb][0].ToString();
cblPinakes.Items.Add(obj);

}

DataTable dt = dataSetPinakes.Tables[0].Copy();
dt.Clear();
dt.ReadXml(“Tables.xml”);

DataRow[] rowsel = dt.Select();
for (int ii = 0; ii < rowsel.Length; ii++)
{
if (rowsel[ii][1].ToString() == “1″)
{
CheckItem(rowsel[ii][0].ToString());
}
}

}
catch (Exception ex)
{ MessageBox.Show(ex.ToString()); }

}

isIdentity sql query

select COLUMNPROPERTY(object_id(‘tmp’),’id1′,’isIdentity’)
sp_configure ‘allow update’, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat – 1
where id = object_id(‘tmp’)
and name = ‘id1′
go
exec sp_configure ‘allow update’, 0
go
reconfigure with override
go

Function c# run sql destination

public void runsqldestination(string query)
{

string sqlupd = query;

SqlCommand cmd6 = new SqlCommand(sqlupd, MSSqlcon2);
cmd6.CommandTimeout = 99920;

try
{
cmd6.ExecuteNonQuery();

}

catch
{ MessageBox.Show(” Error ! “); }
}

SQL create a procedure with update and insert a other table

CREATE PROCEDURE dbo.sp_UpdateLibList
@TableId     as numeric(18,0),
@descr1     as nvarchar(2048),
@descr2    as nvarchar(2048) = ‘ ‘,
@reftableid     as numeric(18,0),
@seealso     as nvarchar(2048),
@newsee    as numeric(18,0),
@UserID     as numeric(18,0)
AS
BEGIN
DECLARE    @Event_ID as Numeric(18,0),
@Table_Code     as nvarchar(50)

SET NOCOUNT ON
select @Table_Code = ( select Top 1 Table_Code from LibTables where Field_Code = ( select fieldcode from liblist where tableid = @tableid) )
if @Table_Code is not null
begin
UPDATE LibList
SET     descr1         = @descr1 ,
descr2         = @descr2 ,
reftableid     = @reftableid,
seealso        = @seealso,
newsee        = @newsee
WHERE tableid        = @tableid

INSERT INTO LibEvent ( Event_Type, TableId, Table_Code)
VALUES ( ‘U’, @TableId, @Table_Code )
SELECT @Event_ID = @@IDENTITY

INSERT INTO UserEvents ( [User_ID], Event_ID )
VALUES ( @UserID, @Event_ID )
end
END

Sql fetch cursors

DECLARE
slip_Update CURSOR FOR
SELECT * FROM SlipsTEST, SlipdetailsTEST
WHERE SlipsTEST.SlipIdOld = SlipdetailsTEST.SlipIdOld
FOR UPDATE;
BEGIN
OPEN slip_Update;
FOR slip_Update IN slip_Update
LOOP
UPDATE SlipdetailsTEST
SET SlipdetailsTEST.SNO = SlipsTEST.SNO
WHERE CURRENT OF slip_Update;
END LOOP;
CLOSE slip_Update;
END;

A smart join with union

SELECT m.MonthName, COALESCE(c.MonthCost, 0)
FROM CostTable c
RIGHT JOIN (
 SELECT 1 As MonthNumber, 'January' As MonthName
 UNION
 SELECT 2, 'February'
 UNION
 ...
 SELECT 3, 'December'
) m
ON m.MonthNumber = MONTH(c.[Month])

A store procedure for microsoft sql to check win

This procedure use time… month and day

CREATE procedure sp_update_WinHands

@month as  nvarchar(10) ,
@day as nvarchar(10)
as

declare @win as float
declare @lose as float
DECLARE @hand INT
declare @query nvarchar(1000)

declare @check as int

set @check=( select count(tableid) from bethistory where month1=@month and day1= @day)

if ( @check=0 )
begin
insert into bethistory(month1,day1) values (@month,@day)
end

SET @hand = 1
WHILE (@hand <=12)
BEGIN

set @win = (select count(id)  from BetHeader
where datepart(m,dt)=@month and datepart(d,dt)=@day
and case when lasthand>@hand then 1 else 0 end=1)

set @lose = (select count(id)  from BetHeader
where datepart(m,dt)=@month and datepart(d,dt)=@day
and case when lasthand>@hand then 1 else 0 end=0)

if( @win=0 ) begin set @win=1 end

set @query= ‘update  bethistory set h’+cast(@hand as nvarchar(10))+’='+cast(@lose as nvarchar(10))+’.00/’+cast(@win as nvarchar(10))+’.00  where month1=’+@month+’ and day1= ‘+@day+”

exec ( @query )

SET @hand = @hand + 1
END

GO

MySQL JDBC Driver. Connect to MySQL from Java

Although the majority of my programming efforts evolve around C# & ASP.NET using C#, I find myself doing more stuff with Java. As with every new language that I work with, there are specific things which are common across all languages, such as reading flat files and parsing delimited strings, reading and writing XML files and of course, connecting to a database that I tend to focus on.

The other day, I found myself needing to connect to a MySQL Database, so I downloaded the JDBC Driver (jar file) that would enable me to connect a small java app and read some data. I downloaded it from the java web site, you can do a search for “download jdbc drivers” and you should find the url.

Next, I created a new project in Eclipse and added a .java file. Be sure to add the .jar file for the jdbc driver to the build path of your Eclipse project or you will get an error when you try use the DriverManager and try to connect to the database.

Here is the code that I used:

Add the following import statement:
import java.sql.*;

Add the following module level variables. 

   static Connection conn;
   static Statement stmt;
   static ResultSet rset;

try {
   Class.forName(”com.mysql.jdbc.Driver”);
   conn = DriverManager.getConnection(
   “jdbc:mysql://localhost:3306/
   yourdatabase”, “loginid”, “loginpw”);

   stmt=conn.createStatement();
   rset = stmt.executeQuery(”SELECT *
        FROM ssfsigma.csaccount”);

   while(rset.next()) {
     System.out.println(rset[1].getString() );
   }
}
catch( Exception ex ) {
   System.out.println(ex.getMessage());
   return;

}

Using the DriverManager object, I call the getConnection() method. The Statement is like the SqlCommand object, so I call CreateStatement() on the Connection object. Once I have my Statement, I create a ResultSet by using the executeQuery() method. After this, I can read each record in the ResultSet using the .next() method.

Notice that the connection string contains localhost:3306. This is the port that is defaulted in the install of MySQL.