|
Hi All,
I have a winform application connected with a sql server 2005/2008 database. Now i have about 200 stored procedures which i would like to distribute with the application and execute as soon as the user creates a new datbase.
i do not want to make use of the specific dll like sqldmo and sqlserver.management, etc.. I want it to have it as clean as possible.
Below the function i use for testing ( forget the fixed file path ). Creating datbase and tables are no problem. However creation of the storedprocedure returns errors.
************** Exception Text **************
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'TO'.
Incorrect syntax near the keyword 'TO'.
Incorrect syntax near '0'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at AgentBase.Business.clsDataModel.fncCreateNewDatabase(String DBNAME, String FILELOCATION) in C:\AgentBase.2010\AgentBase.Business\clsDataModel.cs:line 91
at AgentBase.Gui.usxConnections.butCreateDB_Click(Object sender, EventArgs e) in C:\AgentBase.2010\AgentBase.Gui\usxConnections.cs:line 293
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Any suggestions, tips, tricks.
using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AgentBase.Datalayer;
public static void fncCreateNewDatabase(string DBNAME, string FILELOCATION )
{
SqlConnection myConn = new SqlConnection(Globals.ConnectionString);
string str = "CREATE DATABASE " + DBNAME + " ON PRIMARY " +
"(NAME = " + DBNAME + "_Data, " +
"FILENAME = '" + FILELOCATION + "\\DATA\\" + DBNAME + "_Data.mdf', " +
"SIZE = 10MB, MAXSIZE = 10000MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = " + DBNAME + "_Log, " +
"FILENAME = '" + FILELOCATION + "\\DATA\\" + DBNAME + "_Log.ldf', " +
"SIZE = 5MB, " +
"MAXSIZE = 50000MB, " +
"FILEGROWTH = 10%)";
string myServer = myConn.DataSource.ToString();
SqlCommand myCommand = new SqlCommand(str, myConn);
myConn.Open();
myCommand.ExecuteNonQuery();
myConn.Close();
myConn = new SqlConnection("Server=" + myServer + ";Integrated security=SSPI;database=" + DBNAME + "");
myConn.Open();
SqlCommand cmd = new SqlCommand();
string sourceDir = "C:\\AgentBase.2010\\AgentBase\\bin\\Debug\\database\\";
string[] fileEntries = Directory.GetFiles(sourceDir);
foreach (string fileName in fileEntries)
{
FileInfo file = new FileInfo(fileName.ToString());
string script = file.OpenText().ReadToEnd();
cmd.CommandText = script;
cmd.Connection = myConn;
cmd.ExecuteNonQuery();
}
"
IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblPersonGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
PRINT 'Dropping procedure sptblPersonGetAll ...'
DROP PROCEDURE [dbo].[sptblPersonGetAll]
END
GO
PRINT 'Creating procedure sptblPersonGetAll ...'
GO
CREATE PROCEDURE [dbo].[sptblPersonGetAll]
(@PE_ID int
,@PE_RE_ID bigint
,@PE_TYPE bigint
,@PE_LASTNAME nvarchar(50)
,@PE_FIRSTNAME nvarchar(50)
,@PE_INITIALS nvarchar(50)
,@PE_CONFIRMATIONS bit
,@PE_INVOICES bit
,@PE_NEWORDER bit
)
AS
SET NOCOUNT ON
SET XACT_ABORT OFF
DECLARE @ProcName varchar(30)
DECLARE @Error integer
DECLARE @RowCnt integer
DECLARE @ErrMsg nvarchar(1000)
DECLARE @Parameters nvarchar(4000)
SELECT @ProcName = object_name(@@procid)
, @Error = 0
, @RowCnt = 0
SET @Parameters = ''
+ ' PE_ID:' + COALESCE(CONVERT(VARCHAR, @PE_ID), 'NULL')
+ ' PE_RE_ID:' + COALESCE(CONVERT(VARCHAR, @PE_RE_ID), 'NULL')
+ ' PE_TYPE:' + COALESCE(CONVERT(VARCHAR, @PE_TYPE), 'NULL')
+ ' PE_LASTNAME:' + COALESCE(CONVERT(VARCHAR, @PE_LASTNAME), 'NULL')
+ ' PE_FIRSTNAME:' + COALESCE(CONVERT(VARCHAR, @PE_FIRSTNAME), 'NULL')
+ ' PE_INITIALS:' + COALESCE(CONVERT(VARCHAR, @PE_INITIALS), 'NULL')
+ ' PE_CONFIRMATIONS:' + COALESCE(CONVERT(VARCHAR, @PE_CONFIRMATIONS), 'NULL')
+ ' PE_INVOICES:' + COALESCE(CONVERT(VARCHAR, @PE_INVOICES), 'NULL')
+ ' PE_NEWORDER:' + COALESCE(CONVERT(VARCHAR, @PE_NEWORDER), 'NULL')
SELECT [PE_ID]
,[PE_RE_ID]
,[PE_TYPE]
,[PE_LASTNAME]
,[PE_FIRSTNAME]
,[PE_INITIALS]
,[PE_SEX]
,[PE_FUNCTION]
,[PE_PHONE]
,[PE_FAX]
,[PE_MOBILE]
,[PE_WEBSITE]
,[PE_EMAIL]
,[PE_SKYPE]
,[PE_LINKEDIN]
,[PE_FACEBOOK]
,[PE_BIRTHDAY]
,[PE_MEMO]
,[IS_DATECREATED]
,[IS_WHO]
,[IS_DATECHANGED]
,[PE_CONFIRMATIONS]
,[PE_INVOICES]
,[PE_NEWORDER]
,[PE_CONTACTFOR]
,[PE_SPEAKSLANGUAGE]
FROM [dbo].[tblPerson]
WHERE (([PE_ID] = @PE_ID) OR (@PE_ID IS NULL))
AND (([PE_RE_ID] = @PE_RE_ID) OR (@PE_RE_ID IS NULL))
AND (([PE_TYPE] = @PE_TYPE) OR (@PE_TYPE IS NULL))
AND (([PE_LASTNAME] LIKE @PE_LASTNAME + '%') OR (@PE_LASTNAME IS NULL))
AND (([PE_FIRSTNAME] = @PE_FIRSTNAME) OR (@PE_FIRSTNAME IS NULL))
AND (([PE_INITIALS] = @PE_INITIALS) OR (@PE_INITIALS IS NULL))
AND (([PE_CONFIRMATIONS] = @PE_CONFIRMATIONS) OR (@PE_CONFIRMATIONS IS NULL))
AND (([PE_INVOICES] = @PE_INVOICES) OR (@PE_INVOICES IS NULL))
AND (([PE_NEWORDER] = @PE_NEWORDER) OR (@PE_NEWORDER IS NULL))
SELECT @Error = @@Error
, @RowCnt = @@RowCount
IF (@Error != 0)
BEGIN
SELECT @ErrMsg = 'Error getting records. Parameters' + @Parameters
GOTO ENDERROR
END
GOTO ENDOK
ENDERROR:
BEGIN
EXEC spErrorLogInsert 'DB Layer', @ProcName, @Error, @ErrMsg
RAISERROR 25000 @ErrMsg
RETURN 1
END
ENDOK:
RETURN 0
GO
IF ( object_id('dbo.sptblPersonGetAll') IS NOT NULL )
BEGIN
PRINT 'Success: Created procedure sptblPersonGetAll.'
END
IF ( object_id('dbo.sptblPersonGetAll') IS NULL )
BEGIN
PRINT 'ERROR: sptblPersonGetAll was not created successfully.'
END
GO
Kind regards,
Marcel Vreuls
AgentBase
<<A good idea can change your life>>
|
|
|
|
|
|
Hi All,
I have an image on which I'm drawing elements ("masks") using a Graphics object (g.DrawCurve, g.FillRectangle, g.DrawPolygon etc.).
Now, when hovering with the mouse over the image, I need to know whether I'm hovering over a mask (a part that was drawn using the Graphics object).
I couldn't find a way to get a Graphic's drawing pixels... any ideas?
Thanks!
|
|
|
|
|
One thing you could do is this:
- have a bitmap ("bm") the same size as the object you are drawing on, then get its graphics ("gbm") and fill with a white backcolor;
- mimic all your drawing on gbm too, using any color but white;
- when you need to know whether a point (x,y) is mask or not, look at bm.GetPixel(x,y) and the color will tell you.
|
|
|
|
|
Hi Guys,
Just a quick question I have c# code working for communicating with a Serial device using the SerialPort Class (see code below):
private void SendEnq(bool blnIncStep)
{
if (blnIncStep)
{
iStep++;
}
byte[] c = new byte[]
{
0x05,
};
spBPDione.Write(c, 0, c.Length);
Console.WriteLine("OUT: " + ByteToHex(c));
}
private void DataRecvEvent()
{
int bytes = spBPDione.BytesToRead;
byte[] comBuffer = new byte[bytes];
spBPDione.Read(comBuffer, 0, bytes);
Console.WriteLine("IN: " + ByteToHex(comBuffer));
Console.WriteLine("STEP:" + iStep);
if (ByteToHex(comBuffer).Trim() == "06")
{
if (iStep >= 2)
{
}
else
{
byte[] c = new byte[] {
0x02, 0x31, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x31, 0x30, 0x31, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x31,
0x30, 0x30, 0x30, 0x30, 0x31, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x31, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20,
0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x31, 0x20, 0x20, 0x20,
0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x03,
0x02
};
spBPDione.Write(c, 0, c.Length);
Console.WriteLine("OUT: " + ByteToHex(c));
iStep++;
}
}
else if (ByteToHex(comBuffer).Trim() == "07")
{
byte[] c = new byte[] {
0x04,
};
spBPDione.Write(c, 0, c.Length);
Console.WriteLine("OUT: " + ByteToHex(c));
iStep++;
}
else if (ByteToHex(comBuffer).Trim() == "05")
{
if (iStep >= 2)
{
byte[] c = new byte[] {
0x06,
};
spBPDione.Write(c, 0, c.Length);
Console.WriteLine("OUT: " + ByteToHex(c));
}
}
else if (ByteToHex(comBuffer).Trim().StartsWith("02"))
{
if (iStep >= 2)
{
byte[] c = new byte[] {
0x07,
};
spBPDione.Write(c, 0, c.Length);
Console.WriteLine("OUT: " + ByteToHex(c));
}
}
else if (ByteToHex(comBuffer).Trim() == "04")
{
if (iStep >= 2)
{
byte[] c = new byte[] {
0x06,
};
spBPDione.Write(c, 0, c.Length);
Console.WriteLine("OUT: " + ByteToHex(c));
}
}
}
Now this works and will do what its supposed to on the device it is not very nice clean maintainable code, could i refactor this into a class that follows the following protocol to make the code more readable and OO.
My System DIRECTION Physical Device
ENQ (05) ->
<- ACK0 (06)
STX (message) ETX LRC ->
<- ACK1 (07)
EOT (04) ->
<- ACK0 (06)
<- ENQ (05)
ACK0 (06) ->
<- STX (message) ETX LRC
ACK1 (07) ->
<- EOT (04)
ACK0 (06) ->
|
|
|
|
|
There are many ways to implement a comm protocol, the best one very much depends on the requirements on reliability, robustness and performance. Here are two extremes:
1.
A very easy approach would do everything in a synchronous way, which means NOT using events (e.g. DataReceived); e.g. have a separate thread that basically does what your protocol diagram shows, including some explicit delays:
Write ENQ
Wait 100
Read 1 and check for ACK0
Write STX msg ETX LRC
Wait 100
Read 1 and check for ACK1
...
The waits are necessary if you want a good chance of getting the next input all in one read(buf); if you read too early, you might get a partial message.
Warning: You have to decide what should happen when something unexpected is read!
2.
The general approach would be completely asynchronous, and it would use a state machine, so the DataReceived handler would consist of a big switch on the state variable, and a number of cases each handling one input line of your protocol diagram. Here too you must make sure your diagram is complete, i.e. in each state what should happen if arbitrary stuff is received?
And then you should also take care of time-outs: what when no data at all, or an insufficient amount of data, is received? Your app probably doesn't want to wait forever...
|
|
|
|
|
Thanks Luc.
Is the If / Else block in my code not already doing what you talk about with the State Machine pattern?
|
|
|
|
|
Not really, you are switching/if-ing on the incoming data; a real state machine would switch on its own state (i.e. on what it expects), not on what it gets. What it gets would make it change states.
If you are fortunate enough to have a situation where one party is basically asking questions (e.g. all starting with a different byte value) which the other party is to answer in one message, then that isn't really a protocol, and you obviously can switch on the question, as there then is no state whatsoever.
|
|
|
|
|
Quick question, I have created a class for the message protocol using States I think, i'm just wondering if this is the correct approach:
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.IO.Ports;
namespace TEST
{
public class clsTEST
{
public enum CurrentCommsState
{
Idle,
SentENQ,
RecvdENQ,
SentACK0,
RecvdACK0,
SentACK1,
RecvdACK1,
SentMessage,
RecvdMessage,
SentEOT,
RecvdEOT
}
private enum MessageDirection
{
In,
Out
}
public CurrentCommsState TheState = new CurrentCommsState();
private MessageDirection TheDirection = new MessageDirection();
private SerialPort spTest= null;
private byte[] btRecvdMsg = new byte[200];
public clsOpenShift(SerialPort spTheSP)
{
spTEST= spTheSP;
TheState = CurrentCommsState.Idle;
TheDirection = MessageDirection.Out;
}
public void SendAndRecv(out byte[] TheBufferRecvd)
{
TheBufferRecvd = new Byte[200];
if (SendMsg())
{
if (RecvMsg(ref btRecvdMsg, 1))
{
Array.Copy(btRecvdMsg, TheBufferRecvd, 200);
}
}
else if (RecvMsg(ref btRecvdMsg, 1))
{
if (SendMsg())
{
}
}
}
private bool RecvMsg(ref byte[] btOutRecvd, int iBytesToRecv)
{
bool blnRetVal = false;
try
{
while (spOpenShiftSP.BytesToRead == 0)
{
System.Threading.Thread.Sleep(100);
}
byte[] theBuffer = new byte[iBytesToRecv];
spTEST.Read(theBuffer, 0, iBytesToRecv);
ChangeState(theBuffer);
Array.Copy(theBuffer, btOutRecvd, theBuffer.Length);
blnRetVal = true;
}
catch (Exception e)
{
blnRetVal = false;
}
return blnRetVal;
}
private bool SendMsg()
{
bool blnRetVal = false;
try
{
byte[] btToSend = MessageToSend();
ChangeState(btToSend);
if (btToSend != null)
{
spTEST.Write(btToSend, 0, btToSend.Length);
blnRetVal = true;
}
else
{
blnRetVal = false;
}
}
catch (Exception e)
{
blnRetVal = false;
}
return blnRetVal;
}
private void ChangeState(byte[] theMessage)
{
if (TheState == CurrentCommsState.Idle)
{
TheState = CurrentCommsState.SentENQ;
}
else if (TheState == CurrentCommsState.SentENQ &&string.Compare(ByteToHex(theMessage).Trim(),"THE CORRECT ACK (EITHER 06 or 07)") == 0)
{
}
}
private byte[] MessageToSend()
{
if (TheState == CurrentCommsState.Idle)
{
byte[] ENQ = new byte[] { 0x05 };
return ENQ;
}
else if (TheState == CurrentCommsState.SentENQ)
{
byte[] STX_MSG_ETX_LRC = new byte[] {
0x02, 0x31, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x31, 0x30, 0x31, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x31,
0x30, 0x30, 0x30, 0x30, 0x31, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x31, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20,
0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x31, 0x20, 0x20, 0x20,
0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x03,
0x02
};
return STX_MSG_ETX_LRC;
}
else if(TheState == CurrentCommsState.SentMessage)
{
byte[] EOT = new byte[] { 0x04 };
return EOT;
}
else if (TheState == CurrentCommsState.SentEOT)
{
return null;
}
else
{
return new byte[1];
}
}
private string ByteToHex(byte[] comByte)
{
StringBuilder builder = new StringBuilder(comByte.Length * 3);
foreach (byte data in comByte)
{
builder.Append(Convert.ToString(data, 16).PadLeft(2, '0').PadRight(3, ' '));
}
return builder.ToString().ToUpper();
}
}
}
I will eventually change the STX_Msg to use Member Vars to populate the correct message not a hardcoded set of values as it is currently.
Also calling the code like so:
byte[] btRecvdMsg = null;
clsTest COMMS = new clsTest(spSerialPort);
while (COMMS.TheState != clsTest.CurrentCommsState.RecvdEOT)
{
COMMS.SendAndRecv(out btRecvdMsg);
System.Threading.Thread.Sleep(100);
}
Am I on the right tracks here?
Thanks in Advance
|
|
|
|
|
Hi Keith,
KeithF wrote: Am I on the right tracks here?
yes, well, maybe. I did not study your code in much detail, I do see a state-machine-like approach, which could be fine; however I have issues in several aspects:
1. I'm not sure I know enough about your "protocol" to judge the approach. As I said, what you need as an implementation heavily depends on your protocol.
e.g. what should happen when an unexpected response is received (say you send an ENQ and then receive an ETX where it "should" have been an ACK)
2. I didn't see your actual transmit and receive operations; is your receiver operating asynchronously (i.e. using a completion event)? or blocking (i.e. just read-and-wait-till-done)?
3. You seem to somewhat merge, then split, inbound and outbound messages; not sure why.
4. Whatever the goals and needs, I'm pretty sure my code would look quite differently, but that is a matter of style. In particular I'm not fond of methods that need data, don't get it as a parameter, and then go fetch it somehow (as your SendMsg-MessageToSend pair is doing).
Here is what I might consider doing if your protocol is somewhat close to what I think you intend:
- I would opt for (pseudo)synchronous transmit and asynchronous receive;
- so I would transmit using SP.Write() and act as if the data is gone instantly (if necessary just include a sleep period that corresponds to the normal transmission time, probably not necessary at all);
- and I would not read except inside the DataReceived handler.
- that would halve the number of states I need, as each state typically would be an "awaiting" state, say "waiting for ACK0";
DataReceived should check the next inbound message;
if it is an ACK0, send a new message (synchronously!), adapt the state (say "waiting for "ACK1"), and return;
if it isn't, send a new message ("aborting"), set state="IDLE" (=waiting for anything), and return;
- so that boils down to a big switch inside DataReceived, switching to the current state (what am I expecting?), then looking at what actually was received, react (probably by sending something) and adapt the state, so the next DataReceived switch will go to another case.
That would work for simple cases; it would not cope well with slow transmission conditions: if you want overlaps (comparable to overlapped file I/O in Windows) or if your state actions are too large to handle in DataReceived itself, you may need a more complex scheme, maybe an input queue, etc.
Hope this helps. But once again, as I said, it all depends on what you need. It would not make sense to create a 1000 LOC class for a thermometer that sends one or two strings per second.
|
|
|
|
|
Dear developers,
Does someone knows how can I iterate all open child forms from the mdi parent
And for each form save the report to one excel file?
|
|
|
|
|
Hope thsi will help you
foreach (Form frm in Application.OpenForms)
{
}
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hi,
the problem i accessing the reports from each form and saving it to one workbook
is it possible?
foreach(Form frm in this.MdiChildren) {
saving each report to a workbook
}
|
|
|
|
|
|
How To Convert The English To Gujarati in The Asp.net With C#
|
|
|
|
|
Language translation, even between similar languages (which Gujarati and English aren't) is not a facile task.I suggest you use an online service like google translate, or find a commercial product that does this. Even so, no translation software is 100% accurate.
|
|
|
|
|
This[^] is a fairly complete guide on this topic.
Its always easier to plan localization in advance (before development starts).
If the site already exists, it will be harder to implement localization.
|
|
|
|
|
|
Hi,
i work with asp.net c#, how can we insert multiple lines text (label) in a single row of datagridview?
Thank you verry mutch.
|
|
|
|
|
Probably by asking in the ASP.NET forum!
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
|
Hey All,
Just d/l your checkers game...http://www.codeproject.com/KB/game/DirectXBoardGameEngine.aspx[^]
However when I open it I am asked to convert it. When I try to build the game it says 'TypeLoadException was unhandled'
Method 'Initialize' in type 'Bornander.Games.Checkers.Direct3D.CheckersModelRepository' from assembly 'Bornander Games Checkers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.
Please advise?
Thanks in advance
|
|
|
|
|
You haven't downloaded my code here, you've downloaded Frederik Bornander's code from that article. Funnily enough, there's a forum at the bottom of the article (all the articles have forums) where you can ask THE ORIGINAL AUTHOR about their code. After all, they are the experts in their code and they are best placed to answer your questions.
|
|
|
|
|
|
MSDN wrote: The ?? operator is called the null-coalescing operator and is used to define a default value for a nullable value types as well as reference types. It returns the left-hand operand if it is not null; otherwise it returns the right operand.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
