SQL Server 2005, Usage % all databases using performance counters!! - C# Discussion Boards

rkherath3-Apr-07 22:55

3-Apr-07 22:55

Hi,

ya its like this i have created oledb connection to create connection to the dbase tales.

I have 112 folders containing 6tdbase tables in each.

coding:

//reading from dbase
public void PluFill(string DBFpath)
{
try
{
oledbConnectionMethod(DBFpath);
oledbselctString = "SELECT PLU_NUM AS SKU,DESC AS Description,O_CP*Q_OPEN AS OpeningCP,Q_OPEN AS OpeningQty,Cost_Price*Qty AS ClosingCP,Qty AS ClosingQty FROM PLU";
oledbSelectMethod(oledbselctString);
oledbDataAdapterMethod();
oledbDataadapter.Fill(this.dsPlu);
}
catch (Exception Exp)
{
AppendToFile(DBFpath, Exp.ToString() + oledbselctString.ToString());
}

}

//writing to sql:

public void PluUpdate(string locationNam,string Year,string Month)
{
//opening the sql connection
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}

//passing the dataset values to the data table
DataTable dt = dsPlu.Tables[0];

SqlCommand sqlInsertCommand=new SqlCommand();
sqlInsertCommand.Connection = sqlConnection;
//reading from the data table and wrinting to the sql table
foreach (DataRow dr in dt.Rows)
{

try
{
if (dr["OpeningCP"].ToString() == "")
dr["OpeningCP"] = 0;

if (dr["OpeningQty"].ToString() == "")
dr["OpeningQty"] = 0;

if (dr["ClosingCP"].ToString() == "")
dr["ClosingCP"] = 0;

if (dr["ClosingQty"].ToString() == "")
dr["ClosingQty"] = 0;

sqlInsertCommand.CommandText = "Insert into tblBalance_Monthly(cYear,cMonth,cLocationCode,cCategoryCode,cSubCategoryCode,cSKU,cDescription,nO_CostPrice,nO_Qty,nC_CostPrice,nC_QTY) values(\'" + Year + "\',\'" + Month + "\',\'" + locationNam + "\',\'" + dr["SKU"].ToString().Substring(0, 2) + "\',\'" + dr["SKU"].ToString().Substring(0, 3) + "\',\'" + dr["SKU"] + "\',\'" +
dr["Description"].ToString() + "\'," + dr["OpeningCP"] + "," + dr["OpeningQty"] + "," + dr["ClosingCP"] + "," + dr["ClosingQty"] + ")";

sqlDataAdapter.InsertCommand =sqlInsertCommand;

sqlInsertCommand.ExecuteNonQuery();
}
catch (Exception Exp)
{
AppendToFile(locationNam, Exp.ToString() + sqlInsertCommand.ToString());
}

}

}

//and i read the locations of the dbase tables through a class and for each location i perform writing and reding

void callTranfer(string Year, string Month, DateTime TXNDATE, string User_ID, string DriverLoc)
{
//object []retArray=new object[2];
DBCon dbcon = new DBCon();

DataTable dt = dbcon.LocationsFill().Tables[0];
Transfer.TransferClass tran = new Transfer.TransferClass();

int value=0;

foreach (DataRow dr in dt.Rows)
{
string folNam = (dr["Dir"].ToString());
DirectoryInfo forlderPath = new DirectoryInfo(string.Concat(DriverLoc, folNam));
//trnsName = forlderPath.ToString();
tran.PluFill(forlderPath.ToString());
tran.PluUpdate(folNam,Year,Month);
tran.Stk_tranRecieptFill(forlderPath.ToString());
tran.Stk_tranRecieptUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.Stk_tranIssuesFill(forlderPath.ToString());
tran.Stk_tranIssuesUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.salesFill(forlderPath.ToString());
tran.SalesUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.adjustmentsFill(forlderPath.ToString());
tran.adjustmentsUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.reclassificationsFromFill(forlderPath.ToString());
tran.reclassificationsFromUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.reclassificationsToFill(forlderPath.ToString());
tran.reclassificationsToUpdate(Year, Month, folNam, TXNDATE, User_ID);

DisplayText(folNam);
value = value + 1;
backgroundWorker1.ReportProgress(value);
}
}

plz help me

Regards
Ruwandi

rkherath

Re: My Application takes 5 hours to run

V.3-Apr-07 23:37

3-Apr-07 23:37

How many rows are you updating?
If it's a lot you might want to concatenate like 100 or more inserts before executing (delimit with ";") (INSERT INTO ... (...) VALUES (...);INSERT INTO ...

You also might want to try the StringBuilder object instead of concatenating strings.

hope this helps

V.
I found a living worth working for, but haven't found work worth living for.

Re: My Application takes 5 hours to run

rkherath3-Apr-07 23:58

rkherath

3-Apr-07 23:58

Well in one table i ahve to update more than 25000 for sure

also can you explain the answer bit more.

I am really in need of this.

Regards
Ruwandi

rkherath

Re: My Application takes 5 hours to run

Christian Graus4-Apr-07 0:04

Christian Graus

4-Apr-07 0:04

You need to not make a full round trip for each row. Instead, create SQL that updates several records for each round trip. This is your bottleneck.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Re: My Application takes 5 hours to run

V.4-Apr-07 0:09

4-Apr-07 0:09

well instead of concatening string like this:

string test = "Test " + var + "\n'testmore'" + anothervar;

do this:

StringBuilder test = new StringBuilder("Test ");
test = test.Append(var);
test = test.Append("\n'testmore'");
test = test.Append(anothervar);

if you pass more than one query into the ExecuteNonQuery function (for example 100 queries) you save 99 calls to the ExecuteNonQuery function (which itself goes through enough other functions)
I'm not sure how this is done with SQL-Server but for Ms-Acces eg, the query will pass through the ExecuteNonQuery through the OleDb layer, through the mdac layer to the database.

Hope this helps.

V.

Stop smoking so you can: Enjoy longer the money you save.
Moviereview Archive

Re: My Application takes 5 hours to run

joon _vh.3-Apr-07 22:48

joon _vh.

3-Apr-07 22:48

You're gonna have to give us some (a lot) more information before we can help you. Have you stepped through your code, or analyzed it in some other way? Do you know what takes the most time? Have you timed individual lines of code or methods?

Please do so and provide us with the details.

I don't know much about code analysis, but System.Diagnostics has a really cool stopwatch Smile | :)

gluck!

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

Re: My Application takes 5 hours to run

rkherath3-Apr-07 22:59

rkherath

3-Apr-07 22:59

Hi Joon,

thanks for replying.

I already send the code to view. hope you can see. if you can have a look i can send the entire thing.

the longest time is taken for writing to sql. I dnt know why.

Plz help me.

I dnt know how what to do.

Regards
Ruwandi

rkherath

SQL Server 2005, Usage % all databases using performance counters!!

Adeel Chaudhry3-Apr-07 21:26

Adeel Chaudhry

3-Apr-07 21:26

Hello to all,

can anybody help me finding the right "Performance Counter" in visual studio 2005 with the help of which we can get the percentage measure of the usage of databases of SQL Server 2005!

tried several counters but wasnt able to find any which have the usage measure of databases!!!

Thanks in advance Smile | :)

Adeel

add to above...

Adeel Chaudhry3-Apr-07 21:27

Adeel Chaudhry

3-Apr-07 21:27

posted this in C# because i am trying to use it in VC#!! Smile | :)

Query [modified]

Nekshan3-Apr-07 21:09

Nekshan

3-Apr-07 21:09

i have these data in table 'dealer':

dealerid@@@campaignid@@@keyvalue
@1@@@@@@@@3@@@@@@@6
@2@@@@@@@@4@@@@@@@3
@3@@@@@@@@2@@@@@@@5
@4@@@@@@@@1@@@@@@@4
@5@@@@@@@@1@@@@@@@2
@6@@@@@@@@2@@@@@@@1
@7@@@@@@@@3@@@@<@NULL@> = NULL is null value in database without the '@'sign between brackets '<','>' and 'NULL'

'@' = spacebar,to separate the data.

Now i want the dealerid where campaignid = 3 n keyvalue = NULL
So, i queried in queryanalyzer of sqlserver2005:

SELECT dealerid FROM dealer WHERE (keyvalue = NULL) AND (campaignid = 3)

I must get output as 7.
But i dont get any output in its output window, neither i am getting any error.

Hope to get a reply.
regards Nekshan.

-- modified at 3:17 Wednesday 4th April, 2007

Re: Query

Christian Graus3-Apr-07 21:53

Christian Graus

3-Apr-07 21:53

Your post is illegible.

Try keyvalue is null instead of keyvalue = NULL. It's a vagary of SQL, two instances of NULL will not have the same value, so nothing every = NULL.

Re: Query

joon _vh.3-Apr-07 22:33

joon _vh.

3-Apr-07 22:33

Christian Graus wrote:
Your post is illegible.

I second

Christian Graus wrote:
Try keyvalue is null instead of keyvalue = NULL

This one I also second.

WHERE keyvalue IS null

should fix your problem.

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

grid control

Enter the Dragon3-Apr-07 20:34

Enter the Dragon

3-Apr-07 20:34

In the grid control when we scroll the sheet the header must still appear.It must not scroll up. Please give me a solution.

Regards,
Rocky.

Re: grid control

Christian Graus3-Apr-07 20:46

Christian Graus

3-Apr-07 20:46

I don't think any grid that's built in does this by default. You'd have to render a header as a seperate control, then render a grid with no headers.

Re: grid control

joon _vh.3-Apr-07 22:38

joon _vh.

3-Apr-07 22:38

I think a datagridview does. Doesn't it?
Yes it does (tested)

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

Re: grid control

sudhakar_vaddepalli3-Apr-07 23:05

sudhakar_vaddepalli

3-Apr-07 23:05

hi,

There is no option in asp.net2.0 to control header alone.But using custom control, we can solve this.It's better to search for Custom control,

with regrads,
sudhakar.vaddepalli,

Is it possible to use visual effect to show data from different sources?

Khoramdin3-Apr-07 20:28

Khoramdin

3-Apr-07 20:28

Hello everyone,

I have a ListView control which is populated by data from two different data sources. Is there anyway that I can use color or similar sort of visual effect to show the user the data source of each row?

Thank you very much and have a great day.

Khoramdin

Re: Is it possible to use visual effect to show data from different sources?

Christian Graus3-Apr-07 20:32

Christian Graus

3-Apr-07 20:32

You can do this with an icon or text. How are you passing the data in ?

Re: Is it possible to use visual effect to show data from different sources?

Khoramdin3-Apr-07 20:47

Khoramdin

3-Apr-07 20:47

Hello Christian,

Thanx for the respond. I am reading the data from two different CSV files and as I read each line, seperate the values and then populate the correct column in each row.

The following code is for populating one of the CSV files into the ListView:

StreamReader streamReader = new StreamReader("MyTempList.txt");<br />
        while ((line = streamReader.ReadLine()) != null)<br />
        {<br />
            //MessageBox.Show(line);<br />
            split = line.Split(delimiterChars);<br />
            //MessageBox.Show(split[0]);<br />
            //MessageBox.Show(split[1]);<br />
            //MessageBox.Show(split[2]);<br />
<br />
            // Call FindItemWithText, sending output to MessageBox.<br />
            ListViewItem foundItem = listView4.FindItemWithText(split[1]);<br />
            if (foundItem != null)<br />
            {<br />
                //MessageBox.Show("Calling FindItemWithText: " + foundItem.ToString());<br />
                // Do nothing.<br />
            }<br />
            else<br />
            {<br />
                //MessageBox.Show("Calling FindItemWithText: null");<br />
                ListViewItem item4 = new ListViewItem(split[0]);<br />
                item4.SubItems.Add(split[1]);<br />
                //item4.SubItems.Add(split[2]);<br />
                listView4.Items.Add(item4);<br />
            }<br />
        }

Thank you for taking the time and helping out, mate.

Khoramdin

-- modified at 3:02 Wednesday 4th April, 2007

Re: Is it possible to use visual effect to show data from different sources?

Christian Graus3-Apr-07 21:52

Christian Graus

3-Apr-07 21:52

OK, so as you add each item, you set properties on the ListViewITem instance, such as the icon or the color.

Re: Is it possible to use visual effect to show data from different sources?

joon _vh.3-Apr-07 22:46

joon _vh.

3-Apr-07 22:46

I would make a method sorta like this

private void PopulateStuff(Color color, string imageKey)
{
    //your logic here
    // ...

    item4.BackColor = color;
    item4.ImageList = yourImageListWithSomeCoolIcons;
    item4.ImageKey = imageKey;

    // ...
}

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

Handle key in all of application

MHASSANF3-Apr-07 20:22

MHASSANF

3-Apr-07 20:22

hi i have the windows application and want when the user press esc the application exited but i don't know the focus now in the which control

how can i do it ?

MHF

Re: Handle key in all of application

Christian Graus3-Apr-07 20:33

Christian Graus

3-Apr-07 20:33

You can set a keypreview property on the form, I thought, which means the form gets to preview all keys.

Re: Handle key in all of application

MHASSANF3-Apr-07 21:34

MHASSANF

3-Apr-07 21:34

is there some things like this to mouse events ??

mHF

Re: Handle key in all of application

Stefan Troschuetz3-Apr-07 21:49

Stefan Troschuetz

3-Apr-07 21:49

MHASSANF wrote:
is there some things like this to mouse events ??

As far as I know there is nothing as easy as simply enabling KeyPreview. Instead you'll have to use hooks that however are well covered by many internet resources. You can start from this CP article Using Hooks from C#[^]

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rick Cook

www.troschuetz.de

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.