包含c回写sqlserver的词条

c#怎么书写连接sqlserver数据库的语句

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Collections;

using System.Net;

namespace HR_Checker.common

{

    class sqldate_111

    {

        private string conn = "server=127.0.0.1;database=main;uid=sa;pwd=@12345;Max Pool Size = 1000;Connection Timeout=180";

        public SqlConnection ExceCon()

        {

            SqlConnection connection = new SqlConnection(this.conn);

            connection.Open();

            return connection;

        }

        public SqlDataReader ExceDr(string cmdtxt)

        {

            SqlCommand command = new SqlCommand(cmdtxt, this.ExceCon());

            return command.ExecuteReader();

        }

        public DataSet ExceDS(string cmdtxt, string tblName)

        {

            SqlConnection connection = this.ExceCon();

            DataSet dataSet = null;

            try

            {

                SqlCommand command = new SqlCommand(cmdtxt, connection);

                SqlDataAdapter adapter = new SqlDataAdapter();

                adapter.SelectCommand = command;

                dataSet = new DataSet(tblName);

                adapter.Fill(dataSet);

            }

            catch

            {

                connection.Close();

            }

            return dataSet;

        }

        public bool ExceSQL(string cmdtxt)

        {

            bool flag;

            SqlCommand command = new SqlCommand(cmdtxt, this.ExceCon());

            try

            {

                command.ExecuteNonQuery();

                flag = true;

            }

            catch

            {

                flag = false;

            }

            finally

            {

                this.ExceCon().Close();

            }

            return flag;

        }

    }

}

你在外边引用这个类  sqldate_111 data = new common.sqldate_111()

下面

DataSet ds = data.ExceDS(sql, "dd");

DataTable dt = ds.Tables[0];

dt 是返回表

this.data111.ExceSQL(insertsql); 这个是执行sql语句

记得修改命名空间

相关问答

Q1: 如何用c#实现把socket接收的数据写入sqlserver数据库

Asp.net中C#使用Socket发送和接收TCP数据的方法。

具体程序代码如下：

using System;

using System.Collections.Generic;

using System.Net;

using System.Net.Sockets;

using System.Text;

namespace ConsoleApplication1

{

    public static class SocketTest

    {

        private static Encoding encode = Encoding.Default;

        /// summary

        /// 监听请求

        /// /summary

        /// param name="port"/param

        public static void Listen(int port)

        {

            Socket listenSocket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);

            listenSocket.Bind(new IPEndPoint(IPAddress.Any, port));

            listenSocket.Listen(100);

            Console.WriteLine("Listen " + port + " ...");

            while (true)

            {

                Socket acceptSocket = listenSocket.Accept();

                string receiveData = Receive(acceptSocket, 5000); //5 seconds timeout.

                Console.WriteLine("Receive：" + receiveData);

                acceptSocket.Send(encode.GetBytes("ok"));

                DestroySocket(acceptSocket); //import

            }

        }

        /// summary

        /// 发送数据

        /// /summary

        /// param name="host"/param

        /// param name="port"/param

        /// param name="data"/param

        /// returns/returns

        public static string Send(string host, int port, string data)

        {

            string result = string.Empty;

            Socket clientSocket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);

            clientSocket.Connect(host, port);

            clientSocket.Send(encode.GetBytes(data));

            Console.WriteLine("Send：" + data);

            result = Receive(clientSocket, 5000 * 2); //5*2 seconds timeout.

            Console.WriteLine("Receive：" + result);

            DestroySocket(clientSocket);

            return result;

        }

        /// summary

        /// 接收数据

        /// /summary

        /// param name="socket"/param

        /// param name="timeout"/param

        /// returns/returns

        private static string Receive(Socket socket, int timeout)

        {

            string result = string.Empty;

            socket.ReceiveTimeout = timeout;

            Listbyte data = new Listbyte();

            byte[] buffer = new byte[1024];

            int length = 0;

            try

            {

                while ((length = socket.Receive(buffer))  0)

                {

                    for (int j = 0; j  length; j++)

                    {

                        data.Add(buffer[j]);

                    }

                    if (length  buffer.Length)

                    {

                        break;

                    }

                }

            }

            catch { }

            if (data.Count  0)

            {

                result = encode.GetString(data.ToArray(), 0, data.Count);

            }

            return result;

        }

        /// summary

        /// 销毁Socket对象

        /// /summary

        /// param name="socket"/param

        private static void DestroySocket(Socket socket)

        {

            if (socket.Connected)

            {

                socket.Shutdown(SocketShutdown.Both);

            }

            socket.Close();

        }

    }

}

Q2: C#如何编写一段程序，能控制SqlServer数据库服务重启？

public string RunDosCommand(string command)

{

Process process = new Process();

process.StartInfo.FileName = "cmd.exe";

process.StartInfo.UseShellExecute = false;

process.StartInfo.RedirectStandardInput = true;

process.StartInfo.RedirectStandardOutput = true;

process.StartInfo.RedirectStandardError = true;

process.StartInfo.CreateNoWindow = true;

process.Start();

process.StandardInput.WriteLine(command);

process.StandardInput.WriteLine("exit");

return process.StandardOutput.ReadToEnd();

}

command 参数可以输入如

net start mssqlserver

net stop mssqlserver

Q3: C#获取 sqlserver 存储过程返回多表数据

返回的时候用dataset就可以,比如你存储过程这样写

select * from A

select * from B

select * from C

select * from D

select * from D

在执行完存储过程后返回一个dataset

dataset ds = 执行存储过程

ds.tables[0]就是表A

ds.tables[1]就是表B

ds.tables[2]就是表C

剩下的依次类推.

Q4: C#怎么调用sqlserver的自定义函数

sql server 自定义函数的使用

自定义函数

用户定义自定义函数像内置函数一样返回标量值，也可以将结果集用表格变量返回

用户自定义函数的类型:

标量函数:返回一个标量值

表格值函数{内联表格值函数、多表格值函数}:返回行集（即返回多个值）

1、标量函数

Create function 函数名（参数）

Returns 返回值数据类型

[with {Encryption | Schemabinding }]

[as]

begin

SQL语句(必须有return 变量或值)

End

Schemabinding :将函数绑定到它引用的对象上（注：函数一旦绑定，则不能删除、修改，除非删除绑定）

Create function AvgResult(@scode varchar(10))

Returns real

As

Begin

Declare @avg real

Declare @code varchar(11)

Set @code=@scode + ‘%’

Select @avg=avg(result) from LearnResult_baijiali

Where scode like @code

Return @avg

End

执行用户自定义函数

select 用户名。函数名 as 字段别名

select dbo.AvgResult(‘s0002’) as result

用户自定义函数返回值可放到局部变量中，用set ,select,exec赋值

declare @avg1 real ,@avg2 real ,@avg3 real

select @avg1= dbo.AvgResult(‘s0002’)

set @avg2= dbo.AvgResult(‘s0002’)

exec @avg3= dbo.AvgResult ‘s0002’

select @avg1 as avg1 ,@avg2 as avg2 ,@avg3 as avg3

函数引用

create function code(@scode varchar(10))

returns varchar(10)

as

begin

declare @ccode varchar(10)

set @scode = @scode + ‘%’

select @ccode=ccode from cmessage

where ccode like @scode

return @ccode

end

select name from class where ccode = dbo.code(‘c001’)

2、表格值函数

a、 内联表格值函数

格式：

create function 函数名（参数）

returns table

[with {Encryption | Schemabinding }]

as

return(一条SQL语句)

create function tabcmess(@code varchar(10))

returns table

as

return(select ccode,scode from cmessage where ccode like @ccode)

b、 多句表格值函数

create function 函数名（参数）

returns 表格变量名table (表格变量定义)

[with {Encryption | Schemabinding }]

as

begin

SQL语句

end

多句表格值函数包含多条SQL语句，至少有一条在表格变量中填上数据值

表格变量格式

returns @变量名 table (column 定义| 约束定义 [,…])

对表格变量中的行可执行select,insert,update,delete ， 但select into 和 insert 语句的结果集是从存储过程插入。

Create function tabcmessalot (@code varchar(10))

Returns @ctable table(code varchar(10) null,cname varchar(100) null)

As

Begin

Insert @ctable

Select ccode,explain from cmessage

Where scode like @code

return

End

Select * from tabcmessalot(‘s0003’)

来自：

C#调用SQL自定义函数返回值

代码

1 --SQL自定义函数：

2

3 CREATE FUNCTION [GetProjectID] (@headStr nvarchar(10),@date datetime)

4 )

5

6 RETURNS NVARCHAR(200)

7

8 AS

9

10 BEGIN

11

12 --不能在自定义函数中用INSERT INTO

13

14 --insert into emos_cust(cust_name,dates)values(

15

16 --@headStr,@date

17

18 --)

19

20 return 'TEST BY HANSHU'

21 END

代码

1 /// summary

2 /// 获取项目文件编号 涂聚文

3 /// /summary

4 private void FileNo()

5 {

6

7 SqlConnection conn = new SqlConnection(connectionString);

8 string strSql = "GetProjectID"; //自定SQL函数

9 SqlCommand cmd = new SqlCommand(strSql, conn);

10 cmd.CommandType = CommandType.StoredProcedure;

11 cmd.Parameters.Add("@headStr", SqlDbType.NVarChar).Value = "ZQ3"; //输入参数

12 cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = System.DateTime.Now.ToShortDateString(); //输入参数

13 cmd.Parameters.Add("@returnString", SqlDbType.NVarChar);

14 cmd.Parameters["@returnString"].Direction = ParameterDirection.ReturnValue; //返回参数

15 try

16 {

17 conn.Open();

18 object o= cmd.ExecuteScalar();

19

20 this.txtAFileNO.Text = cmd.Parameters["@returnString"].Value.ToString();

21

22 //Response.Write("");

23

24 }

25 catch (Exception ex)

26 {

27

28 this.txtAFileNO.Text = ex.Message;

29

30 }

31 finally

32 {

33

34 if (!(conn.State == ConnectionState.Closed))

35 {

36

37 conn.Close();

38

39

40 }

41

42 }

43

44

45 }