Writing SQL statement in C# - C# Discussion Boards

dan!sh 3-Mar-09 7:16

dan!sh

3-Mar-09 7:16

Connect to the database and get data into a dataset. All you need is a query to get the data from all the tables into one. (I am suspecting Union or join like scenario here.)You do have options of having your own column names. A bit of search in the documentation will help you.

Time is the best teacher; unfortunately it kills all of its students.

जय हिंद

haroon19803-Mar-09 7:35

haroon1980

3-Mar-09 7:35

Thanks..but I am not very expert and quick on wat y mentioned...Can you pls be more in steps if possible?

Regards,

Eddy Vluggen3-Mar-09 8:09

Eddy Vluggen

3-Mar-09 8:09

There's an article here[^] about Microsoft Access and C#, featuring a loadDataGrid method.

Enjoy Smile | :)

I are troll Smile | :)

Writing SQL statement in C#

haroon19803-Mar-09 11:38

haroon1980

3-Mar-09 11:38

THANKS Eddy

Deepali Khalkar3-Mar-09 6:39

Deepali Khalkar

3-Mar-09 6:39

I am developing an application on Win mobile 5.0 emulator in C# using Visual studio 2008.
Can anyone let me know how to write an SQL statement in C# which returns the row count?
Is the parameter required to store the count value?
I will be greatefull if someone helps with the code snippet for the same.

I have tried the following code for the select statement(the select stmt does not return the row count value) and is working fine.

String uname_in='abc';
SqlCeConnection conn;
SqlCeCommand comm;
SqlCeDataAdapter adap = new SqlCeDataAdapter();
String connstring = @"Data Source=Program Files\signup\signupdb.sdf";
conn = new SqlCeConnection(connstring);
conn.Open();
comm = new SqlCeCommand();
comm.Connection = conn;
comm.CommandText = "select * from signuptbl where username =@uname";
SqlCeParameter para = new SqlCeParameter();
para.ParameterName = "@uname";
para.Value = uname_in;
comm.Parameters.Add(para);
adap.SelectCommand = comm;
System.Data.DataTable dt = new DataTable();
ap.Fill(dt);
psw1 = dt.Rows[0][1].ToString();

What changes should be done in the above code for writing a select stmt the return the number of rows selected?

thanks in advance Big Grin | :-D

Re: Writing SQL statement in C#

Yusuf3-Mar-09 8:32

Yusuf

3-Mar-09 8:32

Deepali Khalkar wrote:
the select stmt does not return the row count value) and is working fine.

So, what you saying is the code works fine but your didn't get any rows returned

Deepali Khalkar wrote:
comm.CommandText = "select * from
signuptbl where username =@uname";

What changes should be done in the above code for writing a select stmt the return the number of rows selected?

your select statement looks fine. And you mentioned it works. there is a condition on your statement

where username =@uname";

may be you don't have data that satisfied your condition.

Yusuf

Cannot use Java generated DES Key in C#

karpolu3-Mar-09 6:13

karpolu

3-Mar-09 6:13

Greetings,

I am developing an application that reads a DES encrypted password generated using Java in a properties file and decrypts it, using DESCryptoServiceProvider. I am provided with the key string originally generated with Java using DES.

Does anyone have an example or sample code that converts/uses a Java generated key to decrypt a Java generated password in C#? After removing the dashes, I get the "Specified key is not a valid size for this algorithm" exception.

I've tried both of these lines of code and get the same error:

static byte[] key = ASCIIEncoding.ASCII.GetBytes(dashKey.Replace("-", ""));
OR
static byte[] key = Encoding.ASCII.GetBytes(dashKey.Replace("-", ""));

The error occurrs at either of the below lines of code I tried:

cryptoProvider.Key = key;
OR
ICryptoTransform decryptor = cryptoProvider.CreateDecryptor(key, iv);

Thanks,
Karl

Re: Cannot use Java generated DES Key in C#

Calin Tatar3-Mar-09 10:02

Calin Tatar

3-Mar-09 10:02

Have you tried with different CipherModes?
Are you sure you are not removing to many dashes?

Calin

Re: Cannot use Java generated DES Key in C#

karpolu3-Mar-09 11:33

karpolu

3-Mar-09 11:33

I figured it out. The key needed to be converted into an 8 byte array.

public static byte[] toBytes(string text)
{
    char[] dash = { '-', '\n', '\r' };
    String[] values = text.Split(dash);
    byte[] keyArray = new byte[8];
    for (int i = 0; i < values.Length; i++)
    {
        int elem = Convert.ToInt32(values[i]);
        elem = elem & 0x00FF;
        keyArray[i] = (byte)elem;
    }
    return keyArray;
}

Thanx,
Karl

Default oreintation landscape ,datetime and Tabname in the footer when user print the excel sheet by default.

vikram_asv3-Mar-09 5:59

vikram_asv

3-Mar-09 5:59

Hello all,
Could anyone tell me how to setup orientation to landscape, and datetime , and tabname in footer when user print the excel sheet by default. i am sending my excel code.Could any one tell what i need to do i search in google but no luck I need In C#.

public static void excelprint(DataSet source, string fileName)
{
bool status = false;
try
{
System.IO.StreamWriter excelDoc;
excelDoc = new System.IO.StreamWriter(fileName);
const string startExcelXML = "<xml version>\r\n<Workbook " +
"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
" xmlns Blush | :O

=\"urn:schemas-microsoft-com:office:office\"\r\n " +
"xmlns:x=\"urn:schemas- microsoft-com:office:" +
"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
"office:spreadsheet\">\r\n <Styles>\r\n " +
"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
"<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
"\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
"\r\n <Protection/>\r\n </Style>\r\n " +
"<Style ss:ID=\"BoldColumn\">\r\n <Font " +
"x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
" ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
"ss:ID=\"Decimal\">\r\n <NumberFormat " +
"ss:Format=\"0.00\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
"ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
"ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
"ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
"</Styles>\r\n ";
const string endExcelXML = "</Workbook>";
int rowCount = 0;
int sheetCount = 1;

excelDoc.Write(startExcelXML);

for (int worksheet = 0; worksheet < source.Tables.Count; worksheet++)
{

excelDoc.Write("<Worksheet ss:Name=\"" + source.Tables[worksheet].TableName + "\">");
excelDoc.Write("<Table>");
excelDoc.Write("<Row>");

for (int x = 0; x < source.Tables[worksheet].Columns.Count; x++)
{
excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
excelDoc.Write(source.Tables[worksheet].Columns[x].ColumnName);
excelDoc.Write("</Data></Cell>");
}
excelDoc.Write("</Row>");

for (int x = 0; x < source.Tables[worksheet].DefaultView.Count; x++)
{
rowCount++;
if (rowCount == 64000)
{
rowCount = 0;
sheetCount++;
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + worksheet + "\">");
excelDoc.Write("<Table>");
}
excelDoc.Write("<Row>"); //ID=" + rowCount + "
for (int y = 0; y < source.Tables[worksheet].Columns.Count; y++)
{
System.Type rowType;
rowType = source.Tables[worksheet].DefaultView[x][y].GetType();
switch (rowType.ToString())
{
case "System.String":
//string XMLstring = x[y].ToString();
string XMLstring = source.Tables[worksheet].DefaultView[x][y].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&", "&");
XMLstring = XMLstring.Replace(">", ">");
XMLstring = XMLstring.Replace("<", "<");
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write(XMLstring);
excelDoc.Write("</Data></Cell>");
break;
case "System.DateTime":
// DateTime XMLDate = (DateTime)x[y];
DateTime XMLDate = (DateTime)source.Tables[worksheet].DefaultView[x][y];
string XMLDatetoString = "";
XMLDatetoString = XMLDate.Year.ToString() +
"-" +
(XMLDate.Month < 10 ? "0" +
XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
"-" +
(XMLDate.Day < 10 ? "0" +
XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
"T" +
(XMLDate.Hour < 10 ? "0" +
XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
":" +
(XMLDate.Minute < 10 ? "0" +
XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
":" +
(XMLDate.Second < 10 ? "0" +
XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
".000";
excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
"<Data ss:Type=\"DateTime\">");
excelDoc.Write(XMLDatetoString);
excelDoc.Write("</Data></Cell>");

break;
case "System.Boolean":
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
//excelDoc.Write(x[y].ToString());
excelDoc.Write(source.Tables[worksheet].DefaultView[x][y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
"<Data ss:Type=\"Number\">");
// excelDoc.Write(x[y].ToString());
excelDoc.Write(source.Tables[worksheet].DefaultView[x][y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.Decimal":
case "System.Double":
excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
"<Data ss:Type=\"Number\">");
// excelDoc.Write(x[y].ToString());
excelDoc.Write(source.Tables[worksheet].DefaultView[x][y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.DBNull":
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write("");
excelDoc.Write("</Data></Cell>");
break;
default:
throw (new Exception(rowType.ToString() + " not handled."));

}
}

excelDoc.Write("</Row>");
}
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");

}
excelDoc.Write(endExcelXML);
excelDoc.Close();
status = true;
// MessageBox.Show("Data exported to excel file successfuly");
}

catch (Exception)
{
status = false;// MessageBox.Show("Error in exporting data to excel file");
}
}

TableAdapter and Transactions. [modified]

hdv2123-Mar-09 5:50

hdv212

3-Mar-09 5:50

Hi I want to use Transactions with TableAdapter class which visual studio dataset designer create for each database objects (tables,...).
i found this article to do this : http://blah.winsmarts.com/2006/06/18/the-definitive-tableadapters--transactions-blog-post.aspx[^]

now u can use transactional tableAdapter like this :

CustomersTableAdapter tableAdap = new CustomersTableAdapter() ;
SqlTransaction tableAdapTran = null ;
SqlConnection tableAdapConn = null ;

try
{
    tableAdapConn = tableAdap.OpenConnection() ;
    tableAdapTran = tableAdapConn.BeginTransaction() ;

    // Do your transactional work

    tableAdapTran.Commit() ;
}
catch (Exception)
{
    tableAdapTran.Rollback() ;
}
finally
{
    tableAdapConn.Close() ;
}

this works fine when u want working with a one TableAdapter. for multuple tableAdapters, u need extend each tableAdapter class for supporting Transactional operation.
In my scenario : i'm dealing with multiple tables (insert,update or delete to/from multiple tables) within a single transaction and i think above method does not correct for this operation. look at this code sample :

CustomersTableAdapter tableAdap = new CustomersTableAdapter() ;
SqlTransaction tableAdapTran = null;
SqlConnection tableAdapConn = null;

OrdersTableAdapter ordersAdapter = new OrdersTableAdapter();
SqlTransaction ordersAdapterTran = null;
SqlConnection ordersAdapterConn = null;

try
{
    tableAdapConn = tableAdap.OpenConnection();
    tableAdapTran = tableAdapConn.BeginTransaction();

    ordersAdapterConn = ordersAdapter.OpenConnection();
    ordersAdapterTran = ordersAdapterConn.BeginTransaction();


    // What i must write here ?


    tableAdapTran.Commit();
}
catch (Exception)
{
    tableAdapTran.Rollback();
}
finally
{
    tableAdapConn.Close();
}

can anybody help me about this issue ?
Note : i don't want to use TransactionScope class.
thanks

modified on Tuesday, March 3, 2009 11:56 AM

Error Loading type during runtime

Mustafa Ismail Mustafa3-Mar-09 3:45

3-Mar-09 3:45

I'm getting the error message:

Could not load type 'RCH.BL.EMR.PatientEmergencyContacts" from assembly 'BL, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"

Please bear in mind the reason this is annoying is because every other use of the same assembly BL.dll works perfectly fine and when I document that line the whole things work like clock work.

Any suggestions?

Code:

<br />
//governorates<br />
Governorates = new RCH.BL.Asclepius.JordanGovernorates().GetAllFromDB();<br />
//nationality<br />
PatientNationality = new RCH.BL.Asclepius.Nationality().GetAllFromDB();<br />
//NumberFormats<br />
NumberFormats = new RCH.BL.Asclepius.NumberFormat().GetAllFromDB();<br />
//Contact Information Type<br />
InformationType = new RCH.BL.Asclepius.ContactInformationType().GetAllFromDB();<br />
//rate of stay<br />
PatientRateOfStay = new RCH.BL.Accounting.RateOfStay().GetAllFromDB();<br />
//Emergency Contacts<br />
EmergencyContactsBL = new RCH.BL.EMR.PatientEmergencyContacts();//this is the one that's causing the gates of hell to open and unleash the demons<br />

Eddy Vluggen3-Mar-09 3:55

Eddy Vluggen

3-Mar-09 3:55

Not much of a suggestion, more wondering why that line breaks the chain.

//Emergency Contacts
EmergencyContactsBL = new RCH.BL.EMR.PatientEmergencyContacts().GetAllFromDB();

Shouldn't it read this?

I are troll Smile | :)

Mustafa Ismail Mustafa3-Mar-09 3:59

3-Mar-09 3:59

It was that. In fact, to be very precise it was this:

EmergencyContacts = new RCH.BL.EMR.PatientEmergencyContacts().GetAllFromDB();

Out of frustration, I decided to create a BL object and from there carry on, but no go.

Eddy Vluggen3-Mar-09 4:10

Eddy Vluggen

3-Mar-09 4:10

It's clean code, and that way you can spot it when something doesn't match the pattern. Unless you have seen the code often, then the brain starts filling in the holes with the things that it expects to see.. Frown | :(

Good hunting Smile | :)

I are troll Smile | :)

Mustafa Ismail Mustafa3-Mar-09 4:11

3-Mar-09 4:11

Eddy Vluggen wrote:
Good hunting

Thanks but I've spent nearly 3 hours and rebooted twice before posting. I'll go and get the hammer...

J4amieC3-Mar-09 4:31

J4amieC

3-Mar-09 4:31

Is there any InnerException?

Do you do anything "funky" in the constructor for PatientEmergencyContacts?

Mustafa Ismail Mustafa3-Mar-09 4:38

3-Mar-09 4:38

No exceptions. The moment the load forms (and I found this out by stepping into it) it crashes. The class has an empty constructor.

Yusuf3-Mar-09 4:36

Yusuf

3-Mar-09 4:36

Is the assembly GAC'ed? It may have an older version GAC'ed

If this is web app, check temporary asp.net folder if they have the right version.

Yusuf

Mustafa Ismail Mustafa3-Mar-09 4:39

3-Mar-09 4:39

No to both and I've manually deleted the bin folder's contents and rebuilt it again but still.

Yusuf3-Mar-09 4:49

Yusuf

3-Mar-09 4:49

Brutal!

Last time I'd reference related issue, I was pulling my hair. Finally decided to so system wide search for the assembly and see how many places I can find it. In my case, it turned out to be I'd multiple versions scattered in few places. This was a project I inherited.

Did you search the system where for the assembly? This is brute force and tedious but it may unearth a clue.

Yusuf

Mustafa Ismail Mustafa3-Mar-09 4:52